Help with normalization
in [Tables]
|
Prev: VSFLEX3.OCX install
Next: Email Validation
From: golfinray on 25 Jan 2010 11:36 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
From: Steve on 25 Jan 2010 12:04 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 santus(a)penn.com "golfinray" <golfinray(a)discussions.microsoft.com> wrote in message news:B06ECA1E-5DFD-40E0-83E6-547C1A7AED25(a)microsoft.com... >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
From: Jeff Boyce on 25 Jan 2010 12:12 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" <golfinray(a)discussions.microsoft.com> wrote in message news:B06ECA1E-5DFD-40E0-83E6-547C1A7AED25(a)microsoft.com... >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
From: golfinray on 25 Jan 2010 13:11 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" <golfinray(a)discussions.microsoft.com> wrote in message > news:B06ECA1E-5DFD-40E0-83E6-547C1A7AED25(a)microsoft.com... > >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 > > > . >
From: Steve on 25 Jan 2010 13:28
Milton, Did you look at the tables I suggested? They take in everything you describe here! Steve "golfinray" <golfinray(a)discussions.microsoft.com> wrote in message news:E0C81FF8-EC5D-4B82-B93D-2E26D0D9173F(a)microsoft.com... > 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" <golfinray(a)discussions.microsoft.com> wrote in message >> news:B06ECA1E-5DFD-40E0-83E6-547C1A7AED25(a)microsoft.com... >> >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 >> >> >> . >> |