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?
Array or Row(s)
Moderator: General Moderators
-
mischievous
- Forum Commoner
- Posts: 71
- Joined: Sun Apr 19, 2009 8:59 pm
Re: Array or Row(s)
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
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)
The all fields for #2 would be integers
- jayshields
- DevNet Resident
- Posts: 1912
- Joined: Mon Aug 22, 2005 12:11 pm
- Location: Leeds/Manchester, England
Re: Array or Row(s)
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
GOOD
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.
ie:
BAD
Code: Select all
cat_id | doc_array
-----------------
1 | 1, 2, 3, 4, 5
2 | 2, 25, 5Code: Select all
cat_id | doc_id
-----------------
1 | 1
1 | 2
1 | 3
1 | 4
1 | 5
2 | 2
2 | 25
2 | 5Re: Array or Row(s)
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.
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)
Option 2 is what I went with, thanks everyone!