Normalisation & Creating Relationships

[dromorkid]

Hi,
I am creating a new database to store information on Jobs. The criteria are;
A Job has a Job # and a Job Name
When allocating jobs, the job is broken down into Divisions. Each job has many Divisions e.g.
Job_Number: 101
Job Name: Remodel the Kitchen
Divisions: Carpenters, Plumers, Tilers.
The divisions have numbers e.g.
Division_ID: 20, Division Name: Carpenters
Division_ID: 30, Division Name: Plumbers
Division_ID: 40, Division Name: Tilers
Each Division has Subdivisions e.g.
Division_ID: 20, Division Name: Carpenters
SubDivision_ID: 21, SubDivisionName: Drywall
SubDivision_ID: 22, SubDivisionName: Cabinet Installers
Division_ID: 30, Division Name: Plumbers
SubDivision_ID: 31, SubDivisionName: Pipe Work
SubDivision_ID: 32, SubDivisionName: Sink/Fixture Installers
I am having trouble setting up the relationship for these tables. The tables I have are
tbl Job Info
Job_Number
Job_Name
tbl Division Info
Division_ID
Division_Name
tbl SubDivision Info
Subdivision_ID
Subdivision_Name
I created another table called tbl Job Division Info where I pulled the PK's from tbl Job Info and tbl Division Info. So this table looks like:
tbl Job Division Info
Job_Number
Division_ID
I did this because one job can have many divisions and one division can be on many jobs. Is this correct?
My other problem is how do I relate tbl SubDivision Info to tbl Division Info ? I have attached what my relationship table looks like but to me I have Division_ID being related from one table to two other tables. Is this good practice or will totally not work?
Please can someone help me? I've posted before with no response at all, I hope I have enough information to get something back.
Thanks.

 

[dromorkid]

Is there something wrong with this question? I'm not having any replies!? Can someone assist me please or tell me what to do in order to explain the question better if it is confusing everyone?!?!

 

[WayPay]

Your questions are a bit hard to spot . Empty lines are not forbidden.

I did this because one job can have many divisions and one division can be on many jobs. Is this correct?

Looks OK to me.

My other problem is how do I relate tbl SubDivision Info to tbl Division Info ?

Looking at the relationship diagram, they are related.

..I have Division_ID being related from one table to two other tables. Is this good practice or will totally not work?

That will do quite nicely .

In short, you seem to have your tables and relationships set up as they should be. So what was the problem you were having?

 

[dromorkid]

I didn't actually run into a problem yet, but I didn't want to go any further if my relationship was wrong. So it is ok to link a PK in one table to Fk's in one or more tables? I haven't done access in a lonnnng time, and it just didn't look right to me.

 

[WayPay]

Yeah, it's OK. You should see the number of relationships a Person table has in a CMS system .

 

[dromorkid]

Hi,

I don't want to keep annoying you, but I have something else I need clarity on.

In my table tblJobInfo, where JobNumber is the PK field, is it good practice to have alot of foreign keys in this table? As far as I can phathom they aren't out of place but it just leaves a whole lot coming back to one table e.g.

tblJobInfo

JobNumber (PK)
JobName
JobAddressID (FK) <--- there can be more than one job at a certain location, but will have different JobNumbers
JobOwnerID(FK) <--- an owner can own multiple jobs, but each with a different JobNumber and different JobAddress

tblJobAddress

JobAddressID(PK)
JobStreetAddress
JobCity
JobState
JobZip

tblJobOwner

JobOwnerID(PK)
JobOwnerName
JobOwnerAddress

I hope I make sense. Thank you
Dromorkid

 

[WayPay]

That all makes perfect sense. You may even want to consider putting all addresses in a separate table with an AddressID. That way you would need 2 relationships between the same 2 tables .

Relationships are not a problem; they make sure that only correct and complete data is stored, which is really the point of using a database. Also, make sure your required fields are actually set to required and to not allow zero length strings. Don't rely on your forms to do that for you.