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

    Unanswered: Database 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
    Apr 2003
    Location
    Edison, NJ / Oakland, NJ (Work)
    Posts
    32

    Talking I'm not a pro...but,

    I'm not a DB pro, actually just starting out myself in creating databases, but here's how I would do it.

    I would go along with what you have already, including the GradYears table. I would first add a field to the users table called UserID, this will decrease the amount of information needed to be stored based on my next step. After each user has an "ID" I would create another table called UserClassOf (or something like that) and it would be structured like this:

    UserID
    SchoolID
    GradYearID
    (*** NO INDEX)

    This would store the information so that sample data could look as so:

    UserID..............SchoolID......GradYearID
    ......1....................004................67
    ......1....................372................71
    ......1....................837................75


    Above would be the example if someone (User 1) went to Junior high, High School, and college. The UserClassOf table links the user (UserID) to the school (SchoolID) as having graduated that specific year (GradYearID). You do not use indexes because it is most likely that none of the three fields are going to be unique for the entire table.


    Your gradyear table could be:

    GradYearID............Year
    ------------------------------
    .......0...................1900
    .......1...................1901
    ....................................
    ....................................
    ......100.................2000
    ......101.................2001


    ----------------------------------------------------------
    A few other suggestions:

    As for your City and State setup, you cannot store the StateID in the City table and do a lookup, while storing only City in the School table because you can have the same city in multiple states (lookup the city named "Wayne", I think there is one in about 40+ states, and sometime multiple per state).

    The best way to do this is to have a ZIPCODE, CITY, STATE table (the data is available, probably at a price though) and use the ZIPCODE as an index, this will allow you to use ZIPCODE in the School table to identify the location and will be able to display the other information with a query that uses "SELECT city, state, zipcode FROM Locations WHERE zipcode='07470'" to get the relational information.

    If you have any other questions about my solution feel free to post.

    Hope this helps.
    -----------------------------------
    Smith Hayward
    -----------------------------------

Posting Permissions

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