0

I'm doing some php/mysql work and started to wonder what would be more efficient and what method would have better data integrity.

I've never used method #2 but i have seen it used in systems like a CMS or eCommerce. I use #1 regularly.

Example: When creating a "task" in my system I need to assign 1 OR multiple users to it.

Method #1 In this method I would have a table which would store both task_id and user_id. I would query this table to get the relationship.

Method #2 In this method I would have a column in the task table "users_assigned" this would store a serialized array which I would unserialize when I needed to. e.g. 

$data = array('John', 'Jack', 'Jill');
// after serialization it would look like...
// a:3:{i:0;s:4:"John";i:1;s:4:"Jack";i:2;s:4:"Jill";}

What method is best for storing this type of data in a database?

mylesthe.dev
  • 9,565
  • 4
  • 23
  • 34

2 Answers2

1

If you do not have a strong reason against denormalization (storing the data duplicated in a serialized array, breaking rules of normal forms), I'd stick to separate tables. Fetching is a bit more complicating, needing joins, but you can control relation integrity better in the database (foreign keys), your data is better organized and more clearly defined.

Take a look at this article http://backchannel.org/blog/friendfeed-schemaless-mysql for an extreme case of denormalization. In their case however, they don't fetch any additional linked data to the rowsets (in your case, you probably want to join more info about the users in the serialized array).

vvondra
  • 3,022
  • 1
  • 21
  • 34
  • Thanks I agree, butI just wanted to see if I was missing anything. I have just always wondered why people use method #2... – mylesthe.dev Oct 15 '12 at 21:40
  • Wish I knew. Someone designed part of a system at work like that, which makes it a nightmare to find data. – Kickstart Oct 15 '12 at 21:41
  • 1
    @Kickstart I agree, working with such data can be a real pain. Since you can't control data integrity in the database, you must do it in the application. If it's not present there, you're into some trouble. – vvondra Oct 15 '12 at 21:44
  • @vvondra - fortunately not an issue for me as there is a "catch all" situation. However it makes it a nightmare to search for matching records. – Kickstart Oct 15 '12 at 21:50
  • Thanks for all your comments, just reinfources my decision not to go down this path. – mylesthe.dev Oct 15 '12 at 22:05
0

Method 2 is a bad idea. Pretty much prevents you joining tables (it is possible still, but only with the kind of code you do to prove it can be done rather than to actually use).

Kickstart
  • 21,403
  • 2
  • 21
  • 33