Help with normalization
in [Tables]
|
Prev: VSFLEX3.OCX install
Next: Email Validation
From: BruceM via AccessMonster.com on 25 Jan 2010 15:14 Dates should be in the table to which they apply. If the overall project has a Start Date and an End Date, you would have fields for those in the Project table, as they are attributes of the project. Then you would have date fields (Start and End, I expect) as needed for the Phase or Step (foundation, etc.). You may also have inspection dates. Steve did indeed suggest a table hierarchy you could use, although I wouldn't have gone so far as to insist it is all you need. It seems you may want a description of the inspection taking place, for instance. Several types of jobs such as plumbing or electrical (and others, no doubt) may have at least a rough-in inspection and a finish inspection. In any case, if there is ever a chance of more than one phase, inspection, or anything else, use a separate table. Even if you are "sure" there will never be more than two inspections (for a phase?project?), use a separate table for inspections. I have gone to considerable effort to redo some of my early projects built based on certainties that were anything but. Which leads to another point: Are inspections for phases only, or are there inspections related to the overall project too? Steve did not explain much about his structure (I have a theory about that, but we'll see). The convention he used, which is a reasonable one, is to name the primary key field the same as the table, and the linking field the same as the primary key field of the table to which it is related. So when you see this: TblProjectPhase ProjectPhaseID ProjectID ProjectPhaseID is the primary key field of tblProjectPhase. ProjectID is on the many side of a one-to-many relationship with ProjectID in tblProject. It is one-to-many because one project may have many phases. One Phase may have many (more than one) inspection, so a similar structure applies to tblInspection. Build the tables, create the relationships using Tools >> Relationships, then you can build the queries, and the forms and other elements of the interface. If the design is done correctly, you can pull any data you want. Don't worry too much about that before the basic structure is built. It is of a little concern that you spoke of "some check boxes". Be careful not to store preferences in check boxes. It can be a nuisance later on. There is a good discussion of this point here: http://allenbrowne.com/casu-23.html golfinray wrote: >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. >> Milton >> >[quoted text clipped - 30 lines] >> >> . -- Message posted via http://www.accessmonster.com |