Ideally your structure would work for any product, which means no columns like "waxtype" or "color". Off the top of my head I'm thinking a product table with things like name, description, price, etc. An attribute table. And a productattribute table to link the two.
Your attribute table could be a multipurpose table from which you could group your attributes into categories (i.e. wax type, colors, etc.). Something like this:
IDPrime
IDCategory
AttrName
AttrDesc
The IDCategory field would be a relation to the IDPrime field (a self join). If the IDCategory field is '0', you know it's a "top" level category. This gives you the ability to create a hierarchy with unlimited depth. The data might look like this:
1 0 Candles Burns bright for hours
2 1 Shape Shapes of candles
3 2 Round
4 2 Square
5 2 Pyramid
6 1 Color Colors available
7 6 Red
8 6 Blue
9 6 Gold
10 1 Wax Type
11 10 X
12 10 Y
13 10 Z
You can add as many attributes as you like without having to ever change your database structure. So if they are buying a Candle, you search the attributes for Candles and get Shape, Color and Wax type (scent, logo, etc). The user then picks each attribute. If you code it right, you wouldn't even have to change you code when you add attributes and categories. You then store the user selection in the productattribute table.
The Product table would be pre-populated with the products you offer, including their links to the attributes. But the design is flexible enough to allow a user to create a custom product or customize and existing one. The shopping cart then only contains the link to the product table. Obviously there is a lot more too creating the whole system, but hopefully this gives you some ideas.
On Apr 7, 2004, at 10:39 AM, JOHN MEYER wrote:
Hi,
I'm writing a database for an online candle store. Here's the situation. This store sells all sorts of items including candles. Now the gist is that some of the candles can be made in different types of waxes and some only have one wax. My question is how do I resolve this when I write up the order and write up the line items. This almost seems like it is going to be some sort of a three way join or something.
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
