Normalization and Join Problems

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
charp
Forum Commoner
Posts: 85
Joined: Sun Oct 26, 2003 3:00 pm
Location: Rancho Cucamonga, Calif. USA

Normalization and Join Problems

Post by charp »

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.
visionmaster
Forum Contributor
Posts: 139
Joined: Wed Jul 14, 2004 4:06 am

Re: Normalization and Join Problems

Post by visionmaster »

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.
I would take out the periods (p1) in another table. Why? Because teacher 1 can have e.g. let's say 3 periods a day, another teacher 2 can have 5 periods a day and so on. Here my suggestion:

teacher
+---------------+
| Field |
+---------------+
| teacherID |
| name |
| surname |
+---------------+


period
+---------------+
| Field |
+---------------+
| id |
| period_name |
| period_time |
| teacherID_FK| (Foreign Key->See table teachers)
| roomID_FK | (Foreign Key->See table rooms)
+---------------+

As many periods as you want can be assigned to a teacher.

rooms
+---------------+
| Field |
+---------------+
| roomID |
| extension |
+---------------+

Room 134 can have more than one extension, if there are e.g. 2 phones in a room.

What do you think?

--------------

To your question of joining your 2 tables:

SELECT teacherID, name, roomID, ext FROM teachers t1, phones t2
WHERE t1.p1 = t2.roomID
User avatar
charp
Forum Commoner
Posts: 85
Joined: Sun Oct 26, 2003 3:00 pm
Location: Rancho Cucamonga, Calif. USA

Post by charp »

Okay, I'm following this but only to a point. Part of my problem, as I say, is that I'm having difficulties wrapping my mind around normalization. Another thing is that my original post may not have been clear enough on a particular detail. Let's address that first.

P1 and P2 were used to represent the periods of the day. There are actually 6 but I trimmed it down for the post. I'd actually use p1 through p6. I'd like to store the data is such a way that I can produce table in a web page that shows each staff member's name and their room numbers (and associated extension numbers) by periods. For example:

Code: Select all

Name	p1		p2		p3		p4		p5		p6
Jones	34		34		34		--		34		99
		134		134		134		--		134		299
Smith	 9		 9		--		54		54		54
		109		109		--		254		254		254
Tables are hard to do in the posts -- but now for the parts of your suggestions that I don't understand, starting with this table:
period
+---------------+
| Field |
+---------------+
| id |
| period_name |
| period_time |
| teacherID_FK| (Foreign Key->See table teachers)
| roomID_FK | (Foreign Key->See table rooms)
+---------------+
I can't quite see how this table will look when filled with data. Let's say that there are 10 teachers in my school (a very reduced number). Will there be 10 rows that all contain p1 (for period 1 or first period) but different teacherID_FKs and roomID_FKs? If yes, then that would mean 60 rows total to account for every teacher's location for each period of the day. Is that correct?

Another concept that boggles my mind is how that table gets populated with data. I'm imagining a web form where I enter all the relevant data for a staff member (name and room numbers by periods) and I just can't visualize how the entries in that form translate into data entries for the Periods table above.

When I find some time, I'll make up some tables as you suggest and then test out the join statement you suggested.

Thanks for the help, visionmaster. Any thing else that you or anyone else can throw my way will be greatly appreciated.
visionmaster
Forum Contributor
Posts: 139
Joined: Wed Jul 14, 2004 4:06 am

Post by visionmaster »

Hi charp,

I'll get back to you later, don't have time right now. Now I know more about the data you want in the database, so I can make further thoughts if my suggestion is the right thing.

Regards,
visionmaster
User avatar
charp
Forum Commoner
Posts: 85
Joined: Sun Oct 26, 2003 3:00 pm
Location: Rancho Cucamonga, Calif. USA

Post by charp »

Visionmaster,

Thanks. I'm busy too and not in a particular hurry. I'll check this post every few days to see if you've added any more.

Much appreciated!

Charp
visionmaster
Forum Contributor
Posts: 139
Joined: Wed Jul 14, 2004 4:06 am

Post by visionmaster »

Hello,

I looked at your suggested db-structure, looks o.k. to me.

A db-structure can not be wrong in my opinion. There is always more than one solution for a problem. At the end you have to get on with your db-structure.

Maybe an additional table for the periods would be suggestive, assuming you want to save additional information regarding the periods.

Regards,
visionmaster
Post Reply