Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Posts
    4

    DB Design Question - Want to create a DB similar to classmates/classreunion.com

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: DB Design Question - Want to create a DB similar to classmates/classreunion.com

    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.

  3. #3
    Join Date
    Jun 2003
    Posts
    4
    Thanks Tony! That possibility had never even occured to me! I'll try implementing it and see what I can come up with.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •