[highvo1tage]

Hey everyone! I'm working on a project similar to classmates.com and classreunion.com and I'm having trouble working out the relationships between the tables. Here's what I've come up with so far.

Tables:

1. Users
-- Username
-- Password
-- Firstname
-- Lastname
-- Email

2. States
-- State ID
-- State

3. Cities
-- City ID
-- State
-- State ID

4. Schools
-- School ID
-- School Name
-- City ID

Here's the problem: I'm unable to resolve which table should have a column for graduation year, or if it should be a separate table altogether.
My understanding is that ONE user has ONE grad year, but MANY schools have MANY grad years. Also, ONE school has MANY students (users).

Knowing this, does it make sense to include a column for grad year under the Users table? Or would populating this grad year column under the users table create redundant data that would excessively increase the size of the DB?

Is it a better decision to make a separate table for graduation years which would look something like the following?

5. Grad Years
-- Grad year ID
-- Grad Year

Would this Grad Years table then be linked to the Users and Schools tables?

Or is there a better solution altogether?

I hope this all makes sense.....if not, write back and I'll try to clarify any questions you may about my questions.

Thanks for your help!

Shak

[andrewst]

A user may have "graduated" from many schools - certainly will have attended more than one anyway, which is what counts. So you need an intersection entity for the m:m relationship:

User Schools
-- Username }
-- School ID } PK
-- From Year }
-- To Year

Adding From Year to the PK allows for people who left school A, went to school B, then back to school A - though not if it all happened in the same year.

[highvo1tage]

Thanks Tony! That possibility had never even occured to me! I'll try implementing it and see what I can come up with.