> In this case, cnelson, you can only have a one to one relationship. Why? If the Attribute table keyed on (ProductID,Attribute), surely a 1:M is possible.
> He wants to allow a one to many relationship. Yes, I know. > 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. I agree! > 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. That's what I was getting at with my second suggestion. > 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
