Array or Individual Rows

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

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 Individual Rows

Post by psurrena »

I have a page where you drag and reorder items, their order is stored numerically in a db.
In the end I will have multiple categories where you can reorder objects and an object can appear in multiple categories.
I need to store multi order numbers for each object.

Would it be more efficient to:
a) Have a table where each row is: category id, item id & position array
OR
b) Have a table where each row is: category id, item id, position

So choice "a" is less rows but contains an array and choice "b" is simpler data and more rows.

Any help would be appreciated, thanks!
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Array or Individual Rows

Post by papa »

If you want your objects to appear in more than one category I would suggest using a relational db structure.

Wiki


For example:

Category
ID | NAME

Item
ID | NAME

Category_Item
category_id | item_id
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Re: Array or Individual Rows

Post by psurrena »

I'm already set up that way.

Tables are (for argument sake):
- items
- categories
- items_categories

I'm looking to add a forth table, something like items_categories_order and questioning if the each order should be a row or array.
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Array or Individual Rows

Post by papa »

Why not add the ordering column in items_categories if you want sepparate order per category?
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Re: Array or Individual Rows

Post by psurrena »

Why didn't I see that! Thanks.
Post Reply