If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with normalization
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!!!! -- Milton Purdy ACCESS State of Arkansas |
#2
|
|||
|
|||
Help with normalization
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 "golfinray" wrote in message ... 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!!!! -- Milton Purdy ACCESS State of Arkansas |
#3
|
|||
|
|||
Help with normalization
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" wrote in message ... 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!!!! -- Milton Purdy ACCESS State of Arkansas |
#4
|
|||
|
|||
Help with normalization
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 Purdy ACCESS State of Arkansas "Jeff Boyce" wrote: 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" wrote in message ... 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!!!! -- Milton Purdy ACCESS State of Arkansas . |
#5
|
|||
|
|||
Help with normalization
Milton,
Did you look at the tables I suggested? They take in everything you describe here! Steve "golfinray" wrote in message ... 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 Purdy ACCESS State of Arkansas "Jeff Boyce" wrote: 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" wrote in message ... 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!!!! -- Milton Purdy ACCESS State of Arkansas . |
#6
|
|||
|
|||
Help with normalization
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 he 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 |
Thread Tools | |
Display Modes | |
|
|