Products and Accessories relationship

[pbcomm]

Hi,
I have table "products" which contains all items we carry. Some of these items are an accessories to other items. What I used to have is a different table called "products_to_acc" where i would have 2 fields "pid" and "aid" which are basically the ids of items from the same table "products".
Getting this would not be easy, as I have to use "JOIN" to select the actual product info from the "products" table.
This would also take up a lot of space, when you have 1000 items and 9-10 accessories for most of them.

Another way that I tried, is creating a column "acc" in the products table and write the ids of accessories to that field like an array "1,55,236,582,25". These saves space and makes a difference in query as i can "SELECT * FROM products WHERE pid IN (implode(',', $acc))".

Which way would you say is better and what are the benefits of one or the other way? Any other possible solutions to creating this?

Working With: PHP, MySQL
Thank You
Paul B.

[devinemke]

the standard way of handling many-to-many data relationships is the first method you mentioned: using a separate link table with the PK fields from the other tables. the other way (storing comma separated lists in a single field) it is generally a bad idea especially if the lists are likely to change often. let's you want to delete an accessory. if use a separate table it is a simple DELETE query with that accessory ID in the WHERE clause. with the other way you would have first grab all of the product records that contain that accessory and parse thru the lists removing that ID (it may well be in a different position in the list on each record) then UPDATE the records with new lists. also, with proper indexing, the separate table method can be much faster.

when doing database design, think vertically, not horizontally.