List:General Discussion« Previous MessageNext Message »
From:Thimble Smith Date:October 7 1999 6:18am
Subject:Re: A Database Structure Question
View as plain text  
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
Thread
A Database Structure QuestionStefan Tryggvason7 Oct
  • Re: A Database Structure QuestionThimble Smith7 Oct
  • Re: A Database Structure QuestionDerek Lavine7 Oct