Normalization and Join Problems
Posted: Mon Sep 06, 2004 10:26 am
I'm going to make the assumption that nothing I've attempted has been correct and start here from the very beginning.
My PHP project is to build a database that includes staff member names, their room numbers for each period of the day, and the corresponding phone extension number for that room. Each staff member has at least 1 period off (hence no room number for that period) and most staff will occupy more than one room throughout the day. Phone extension numbers do not follow a regular pattern in association with the room number (i.e. some extentions are the room number plus 100, others are plus 200 and yet others are randomly assigned).
Having no experience with database normalization, this is what I came up with: put staff names and room numbers in one table and put all the room number/extension numbers in another table.
Here's a simplified version with one staff, two periods, and two room/extensions:
Table: teachers
Field --> Data
---------------------
teacherID --> 1
name --> Jones
p1 --> 60
p2 --> 134
Table: phones
Field --> Data
---------------------
roomID --> 60
ext --> 116
roomID --> 134
ext --> 359
If this design is faulty, you can stop me here and help me out with a better way to set things up. Normalization has so far proven difficult to wrap my mind around, but it feels correct to split things up the way I have.
My next problem was, of course, the proper way to join these two tables in order to produce a coherent table of staff names wtih period-by-period locations and extensions.
I tried all sorts of variations of inner and left joins. Some attempts came close, but none were on target. My biggest problem seems to be *joining* the room numbers and extension numbers.
Sorry for the long drawn out explanation, but I've been spinning my wheels and could use a little traction.
My PHP project is to build a database that includes staff member names, their room numbers for each period of the day, and the corresponding phone extension number for that room. Each staff member has at least 1 period off (hence no room number for that period) and most staff will occupy more than one room throughout the day. Phone extension numbers do not follow a regular pattern in association with the room number (i.e. some extentions are the room number plus 100, others are plus 200 and yet others are randomly assigned).
Having no experience with database normalization, this is what I came up with: put staff names and room numbers in one table and put all the room number/extension numbers in another table.
Here's a simplified version with one staff, two periods, and two room/extensions:
Table: teachers
Field --> Data
---------------------
teacherID --> 1
name --> Jones
p1 --> 60
p2 --> 134
Table: phones
Field --> Data
---------------------
roomID --> 60
ext --> 116
roomID --> 134
ext --> 359
If this design is faulty, you can stop me here and help me out with a better way to set things up. Normalization has so far proven difficult to wrap my mind around, but it feels correct to split things up the way I have.
My next problem was, of course, the proper way to join these two tables in order to produce a coherent table of staff names wtih period-by-period locations and extensions.
I tried all sorts of variations of inner and left joins. Some attempts came close, but none were on target. My biggest problem seems to be *joining* the room numbers and extension numbers.
Sorry for the long drawn out explanation, but I've been spinning my wheels and could use a little traction.