Help with normalization

[golfinray]

I have a table that I need to normalize. It has dates that must be stored,
they are project stop and end points, like foundation started, foundation
completed. What is the best way to store dates? Should they be a separate
table by themselves with just a key? I then have :
Project number
Project School District
Project Building
Project Description
Comments
Some check boxes for N\A
Some check boxes for inspected\inspector
Many thanks!!!!

 

[Steve]

First, I suggest you add ProjectID (autonumber) for the primary key of your
project table.

Assuming there are more than one school district, you need a table for
school districts:
TblSchoolDistrict
SchoolDistrictID
SchoolDistrict

Assuming there are more than one building in any school district, you need a
table for buildings:
TblSchoolBuilding
SchoolBuildingID
SchoolDistrictID
SchoolBuildingName

If there are more than one inspectors, you need a table for inspectors:
TblInspector
InspectorID
FirstName
LastName

A project has many phases like foundation started and foundation completed.
Each phase has a start date and an finish date. You need a table for project
phases:
TblProjectPhase
ProjectPhaseID
ProjectID
ProjectPhaseDescription
StartDate
EndDate

Inspections are made on each phase. Assuming more than one inspection is
made on each phase, you need a table for phase inspections:
TblPhaseInspection
PhaseInspectionID
ProjectPhaseID
InspectionDate
InspectorID

Ypur project table then would look like:
TblProject
ProjectID
SchoolDistrictID
SchoolBuildingID
ProjectNumber
ProjectDescription
Comments

Some check boxes for N\A - not lnowing what these are but most likely they
go in your TblProjectPhase. Tell me more about these and I will advise
further.

Steve
(e-mail address removed)

 

[Jeff Boyce]

Milton

When you say "project", I hear "task" ... are you saying that a "Job" can
have many "Projects"?

Wouldn't the start/stop dates HAVE to be related to/tied to specific
"projects"? (e.g., "foundation")

Would your "projects" have one (and only one) "checkoff", or could they have
many? If many, it sounds like you'd need a one-to-may relationship between
"projects" and "project-checkoffs" (and I'm guessing, a one-to-many
relationship between "checkoffs" and "project-checkoffs"!).

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[golfinray]

Thanks guys. We have one project, many dates. We normally only do one
inspection, two at the most. We have 8 inspectors so I could have a little
table for them. As to dates, we have dates for steps of the projects and
completion date, inspection date. I question how to handle dates that must be
stored. The dates are tied to "A" project. IE, project number 1011-1100-323
has, say 8 dates. from drawings through completion. I am thinking I have to
have a one-to-many, one project, many dates. But then I also have to deal
with District, school, project description. I am just a little confused on
the dates, do they need a primary and foreign, or what.

 

[Steve]

Milton,

Did you look at the tables I suggested? They take in everything you describe
here!

Steve