A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help with normalization



 
 
Thread Tools Display Modes
  #1  
Old January 25th, 2010, 05:36 PM posted to microsoft.public.access.tablesdbdesign
Golfinray
external usenet poster
 
Posts: 1,597
Default 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  
Old January 25th, 2010, 06:04 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default 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  
Old January 25th, 2010, 06:12 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default 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  
Old January 25th, 2010, 07:11 PM posted to microsoft.public.access.tablesdbdesign
Golfinray
external usenet poster
 
Posts: 1,597
Default 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  
Old January 25th, 2010, 07:28 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default 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  
Old January 25th, 2010, 09:14 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:21 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.