SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member markdr's Avatar
    Join Date
    Jul 2006
    Location
    London, UK
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question How to best structure a database for this data?

    I'm in the process of developing a new web program but this is my first time using MySQL properly so could do with a little advice on how to structure the tables to store the data I need for my site.

    Without going into too much detail, each user can add an unlimited number of 'subjects' on their page. Each will have 'name', 'description' etc - all fine so far.

    Each 'subject' can have up to 6 'modules' corresponding to it. For each module there's quite a lot of headings - ie. 'name', 'description', 'score', 'maxscore' etc.

    My initial thought was this:
    - Table called 'users'. For each user one column could be 'subjects' which could store a comma-separated list of 'subjectID's. The reason I think I need this list is I could also store what order the subjects go in.
    - Table called 'subjects'. List of subjects with 'subjectID' as primary key (corresponding to previous table). For each subject one column could be 'modules' which could contain a comma separated list of 'moduleID's
    - Table called 'modules'. List of modules with 'moduleID' as primary key (corresponding to previous table)

    Then the PHP would have the job of parsing the comma separated lists and printing the subject/modules from each table. And both the 'subjects' and 'modules' table would have columns for 'userID' so it could be double-checked that the subjects and modules actually belong to the logged-on user before printing.

    I apologise for my noobieness (and this long post ) but are there any better ways of structuring the database than this? It seems a little...clunky. Also, is there anything else I should take into consideration from the info I've given you?

    Thank you very much.
    "When hell freezes over, it'll be a cool place to snowboard!"

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what you want to do is eliminate the comma-delimited values

    instead, think of a table with one row for each value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict chestertondevelopment's Avatar
    Join Date
    Dec 2005
    Location
    Essex, UK
    Posts
    241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You'll want the following tables, foreign key's are in brackets:

    A subjects table, a users table, a modules table (subjectID) and a table linking the subjects with the users (ID, subjectID, userID)

  4. #4
    SitePoint Member markdr's Avatar
    Join Date
    Jul 2006
    Location
    London, UK
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    what you want to do is eliminate the comma-delimited values

    instead, think of a table with one row for each value
    Ok, as in a 'subjects' table with a row for each subject, with a foreign key being 'userID' to link those subjects to the applicable user? This was what I thought of although I couldn't see how I would store the (user editable) order of these subjects, as in this table they would be in order of adding.
    "When hell freezes over, it'll be a cool place to snowboard!"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    one option is a sequence column

    user editing of multiple item sequences is a common requirement, although the programming can be tricky, because you need to handle "up" and "down" moves

    one tip for this is not to assign sequence numbers consecutively
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

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
  •