In this case, cnelson, you can only have a one to one relationship. He wants to allow a one to many relationship.
Using one field to store all the attribute ids in the product table is what he is trying to do and it is not the best idea. Better use a table in the middle, with product ids and attribute ids, this way he can have one more than one attribute per product. It allows more than one attribute per category of attribute. For instance, languages available in a DVD: DVD titles: id title 1 AAAA 2 BBBB 3 CCCC Attributes: id type_id description 1 1 English 2 1 French 3 1 Spanish Attribute types: id description 1 language attribute links: dvd_id attribute_id 1 1 1 2 1 3 To list all the languages in plain English for DVD #1: Select attributes.description >From attributes_links On attributes_links.attribute_id = attributes.id Where attributes_links.dvd_id = 1 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 5:41 AM To: Scott Haneda Cc: MySql Subject: Re: Best way to design one to many queries > Been wondering this for a while now, I can explain how I do it, but > I am sure there are better ways. > ... Maybe I'm really missing something here but I think that your querying problem arises from bad data design. This is what I'd do: Product table: ProductID some other stuff Attribute table: ProductID Attribute which gives: SELECT Product.something Attribute.Attribute FROM Product LEFT JOIN Attribute ON (Product.ProductID = Attribute.ProductID); Or Product table: ProductID product stuff ProdAttr table: ProductID AttributeID Attribute table: AttributeID other attribute stuff Which makes the query a little more complicated but the attributes a little more flexible. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
