Serializing Data and Schema Design Question

Dev Shed Forums Sponsor:

December 20th, 2012, 03:13 PM

chrismichaels84

Registered User

Join Date: Nov 2012

Posts: 23

Time spent in forums: 4 h 37 m 26 sec
Reputation Power: 0

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

December 20th, 2012, 03:26 PM

r937

SQL Consultant

Dev Shed God 43rd Plane (26000 - 26499 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,443

Time spent in forums: 3 Months 1 Week 2 Days 14 h 21 m 52 sec
Reputation Power: 4141

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

December 20th, 2012, 03:33 PM

chrismichaels84

Registered User

Join Date: Nov 2012

Posts: 23

Time spent in forums: 4 h 37 m 26 sec
Reputation Power: 0

Quote:

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?

December 20th, 2012, 04:10 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,443

Time spent in forums: 3 Months 1 Week 2 Days 14 h 21 m 52 sec
Reputation Power: 4141

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 )
);

December 20th, 2012, 04:16 PM

chrismichaels84

Registered User

Join Date: Nov 2012

Posts: 23

Time spent in forums: 4 h 37 m 26 sec
Reputation Power: 0

You are awesome. Thank you!

Serializing Data and Schema Design Question

Developer Shed Advertisers and Affiliates