#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    31
    Rep Power
    2

    Serializing Data and Schema Design Question


    I am using PHP, though my question isn't a PHP question. When is it appropriate to serialize an array and save it to a database instead of creating relational databases? Here is my specific situation:

    I am creating a project management application as a wordpress plugin for my media company. We have several artistic collaborations going on at any given time. I want to be able to create many projects and have users assigned in different roles to one or more of those projects. So, this is what I've come up with:

    Code:
    -------------------
    Table: Users         |
    -----------------------------------------------------------------
    userid  |   username  |   member_of  |    director_of  |  reader_of  |
    -----------------------------------------------------------------
    1            michael         3, 7                  1, 3              NULL
    -----------------------------------------------------------------
    2            kate             1                      2, 7              2
    -----------------------------------------------------------------
    etc...
    
    
    -------------------
    Table: projects     |
    --------------------------------------------------------------
    project_id  |   directors                  |   members   |    readers   |
    --------------------------------------------------------------
    1                 1/michael, 6/James       etc.                etc.
    --------------------------------------------------------------
    2                 2/kate, 4/Carey            etc.               etc.   
    --------------------------------------------------------------
    etc...
    So I can query either table and parse the strings (or better yet, serialize them as arrays) and have the information I need. Basically, I just need to know by querying the projects who is assigned to that project. I also need to know what projects (and in what capacity) each user is assigned to by querying the user (or multiple users).

    However, I'm sure this can be done better through JOINING these tables. I have racked my brain to figure out the best schema and just come up short.

    I would really appreciate any thoughts from masters, as I am new at this.

    Thank you.
    Last edited by chrismichaels84; December 20th, 2012 at 03:18 PM. Reason: trying to work out spaces in my table
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    storing a comma-delimited list of id numbers in a single column is okay ONLY IF you plan NEVER to search for a specific id within that column

    if you cannot meet that very stringent criterion, then you should normalize the data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    31
    Rep Power
    2
    if you cannot meet that very stringent criterion, then you should normalize the data
    Normalize means create a third table as a cross reference and query that table with JOIN statements? If I do that, how do I organize the users table? If the number of projects changes drastically and often?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Code:
    CREATE TABLE users
    ( userid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , username VARCHAR(37) NOT NULL
    );
    CREATE TABLE projects     
    ( projectid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , descr VARCHAR(37) NOT NULL
    );
    CREATE TABLE members
    ( projectid INTEGER NOT NULL 
    , userid INTEGER NOT NULL
    , PRIMARY KEY ( projectid , userid )
    );
    CREATE TABLE directors
    ( projectid INTEGER NOT NULL 
    , userid INTEGER NOT NULL
    , PRIMARY KEY ( projectid , userid )
    );
    CREATE TABLE readers
    ( projectid INTEGER NOT NULL 
    , userid INTEGER NOT NULL
    , PRIMARY KEY ( projectid , userid )
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    31
    Rep Power
    2
    You are awesome. Thank you!

IMN logo majestic logo threadwatch logo seochat tools logo