Array or Row(s)

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Array or Row(s)

Post 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?
mischievous
Forum Commoner
Posts: 71
Joined: Sun Apr 19, 2009 8:59 pm

Re: Array or Row(s)

Post 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
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Re: Array or Row(s)

Post by psurrena »

The all fields for #2 would be integers
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Array or Row(s)

Post 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.
User avatar
mikemike
Forum Contributor
Posts: 355
Joined: Sun May 24, 2009 5:37 pm
Location: Chester, UK

Re: Array or Row(s)

Post 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.
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Re: Array or Row(s)

Post by psurrena »

Option 2 is what I went with, thanks everyone!
Post Reply