Page 1 of 1

Array or Row(s)

Posted: Tue May 26, 2009 3:43 pm
by psurrena
I have categories and documents that fall under these categories. Is it better for me to:

1) have a table with a field for each category id and a field for an array containing all associated document id's.
example: table - cat_doc, fields - cat_id, doc_array

2) have a table with the field category id and the field document id. That would mean each row would have one document associated with one category.
example: table - cat_doc, fields - cat_id, doc_id

The difference is example 1 would have a large array and example 2 would have many rows. Which is better as it gets larger?

Re: Array or Row(s)

Posted: Tue May 26, 2009 4:21 pm
by mischievous
Personally I would run with option 2.

This way you could run a search through the mysql table easily, just make sure and set your field type accordingly and you shouldnt hit slow downs

Re: Array or Row(s)

Posted: Tue May 26, 2009 4:35 pm
by psurrena
The all fields for #2 would be integers

Re: Array or Row(s)

Posted: Tue May 26, 2009 5:48 pm
by jayshields
I don't know if this is what you're suggesting... but never store more than one value in any one field of any one row.

ie:
BAD

Code: Select all

cat_id | doc_array
-----------------
1      | 1, 2, 3, 4, 5
2      | 2, 25, 5
GOOD

Code: Select all

cat_id | doc_id
-----------------
1      | 1
1      | 2
1      | 3
1      | 4
1      | 5
2      | 2
2      | 25
2      | 5
Ofcourse there are exceptional circumstances when this rule does not apply, but it's unlikely. If you want to know more then read about normalisation and normal form.

Re: Array or Row(s)

Posted: Tue May 26, 2009 7:12 pm
by mikemike
If you choose option 1 (which i recommend AGAINST) then check out the php function serialize, it will put the array into a sotrable value for you, you can then use unserialize to extract it.

Personally I ALWAYS use option 1, as it's easy to code with, easier to read from a human perspective and so long as your database is properly indexed, not particularly slow.

Re: Array or Row(s)

Posted: Tue May 26, 2009 9:52 pm
by psurrena
Option 2 is what I went with, thanks everyone!