At 07:06, 19991007, Stefan Tryggvason wrote:
>Instinct tellse me that the first method would be the better one since comma
>delimited lists are somewhat messy, however, could someone outline the
>benifits of using either method if there are any?
Yes, please use a separate table with two fields, not a comma-separated
list.
How are you going to do joins if you have all your ids in one field?
With a separate table, you do this:
SELECT c.name, c.telephone, i.sku, i.quantity
FROM customers c, items i, orders o
WHERE c.id = o.customer_id
AND i.id = o.item_id
ORDER BY c.name, i.quantity DESC
This is *very* fast in MySQL, too - don't think that because you are
using a separate table you're saving time! Also, the separate table
does not take up more room than your comma-separated list of ids - your
ids can be stored as numbers in the separate table, but they have to be
stored as their string representations in your comma-separated list.
Yech!
If you can give me one reason that storing the ids in a comma-separated
list would be better than in a separate table, I'll be quite impressed
(and, at the same time, quite unimpressed).
Tim
