SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2011
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Why not to put multiple values into single MySQL field?

    Hello,

    Im working on a site that has users, and classes. Each user can be enrolled in one or more classes. Each class has a class code that is added to the users "classcode" field when they enroll in the class. Ive been looking for a way to add multiple values to one field and found that most people advise against it for semantic reasons.

    Im wondering what the better practice would be for this situation. How would I relate a specific user to more than one class without doing it this way?
    The only other option I thought of would be to dynamically add a field to the users table when a class is created but this will raise problems and be a strange workaround in my mind.

    If anybody has any input on this, Id love to hear it, thanks!

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,809
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    1. Do not put multiple vvalues in the same field.

    2. Do not add multiple fields to hold similar content.

    Doing either of those means that your database is no longer properly normalized.

    What you need to do is to add another table that has two fields - user and classcode. You can then delete the classcode field from the first table and use the second table to define anywhere between zero and a trilliion+ classcodes for each user.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Code:
    users
    ====
    id | name
    ---------
    23 | Bob
    
    classes
    ======
    id | class_name
    ---------------
    1 | English
    2 | French
    
    user_class
    ========
    user_ref | class_ref
    ------------------
    23 | 1
    23 | 2
    Elaborating on @felgall ; answer really, that is how your db tables could be structured to give you and idea.

    To get the data out in a single query, so that Bob has classes in English and French, you'd need to read up on how to do table JOINs, and also bone up on the degrees of database normalization.

  4. #4
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,809
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Cups View Post
    that is how your db tables could be structured to give you and idea.
    Except that the students probably all have a student number and I distinctly rememeber that the OP said each class has a class-code so you would use those meaningful fields instead of the id fields. You should only generate ids when there is no value to uniquely identify each row of a table or where the unique identifier is huge.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    My example was only to pique the OPs interest a little further by showing what a referential table was. I bet there are no students called just `Bob` either.

  6. #6
    SitePoint Enthusiast
    Join Date
    Jun 2011
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys! the referential table concept was the missing piece i needed to understanding how these database relationships worked.

  7. #7
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,809
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by chasejbarnes View Post
    Thanks guys! the referential table concept was the missing piece i needed to understanding how these database relationships worked.

    Are you sure you understand the rest of how database normalization works? The piece you were missing is such an early part of the entire concept of how to create normalized relational databases that there are probably other parts of normalization that you also haven't considered.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">


Bookmarks

Posting Permissions

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