Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2011
    Posts
    3

    Problem designing a database (Normalization)

    Hi all,

    I plan to design a website to learn languages where users can regsiter. I have thought of two options to create my database, I don't know if there are more.

    First option:

    I use only one table.

    "users" :

    id | name | email | city | english | french | German

    Second option:

    I use three tables.

    "users" :

    id | name | email | city

    "userlanguages":

    id | iduser | idlanguage

    "languages":

    id | language

    The point is that number of languages will increase in the future and I want to know which advantages/disadvantages each option will offer.

    Thank you very much,

    Atomic_Sam

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the properly normalized structure is ~way~ better

    please note that the "userlanguages" table should ~not~ have its own auto_increment
    Code:
    CREATE TABLE user_languages 
    ( user_id INTEGER NOT NULL
    , lang_id INTEGER NOT NULL 
    , PRIMARY KEY ( user_id, lang_id )
    , INDEX language_users ( lang_id, user_id )
    , FOREIGN KEY ( user_id ) REFERENCES users ( id )
    , FOREIGN KEY ( lang_id ) REFERENCES languages ( id )
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2011
    Posts
    3
    Thank you very much r937 for your quick response.

    I'll take the second solution.

    So the user_languages primary key is composed by two fields and then it's not necessary to use its own auto_increment primary key, right? Becuase it's redundant. It's a more professional way.

    What does " , INDEX language_users ( lang_id, user_id )
    , FOREIGN KEY ( user_id ) REFERENCES users ( id )
    , FOREIGN KEY ( lang_id ) REFERENCES languages ( id )
    " do ?

    Thank you very much again,
    Atomic_Sam

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Atomic_Sam View Post
    So the user_languages primary key is composed by two fields and then it's not necessary to use its own auto_increment primary key, right?
    correct

    Quote Originally Posted by Atomic_Sam View Post
    What does " , INDEX language_users ( lang_id, user_id )
    , FOREIGN KEY ( user_id ) REFERENCES users ( id )
    , FOREIGN KEY ( lang_id ) REFERENCES languages ( id )
    " do ?
    the first line creates an index on the two columns but in reverse sequence from the primary key

    the second and third lines create foreign key relationships to parent tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2011
    Posts
    3
    Thank you very much!!!!!

Posting Permissions

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