[PHPLIB] Off-topic - Database design issue From: Philip Strnad (pdstrnad <email protected>)
Date: 12/30/99

Greetings,

The following isn't directly related to PHPLIB, but since I know there are
some database people on this list, and since I don't have access to any
good database books right now, I thought I'd see if anyone could give me
their input on the following (typical?) DB design problem I have run into.

I have a bunch of products, each of which can be put into one (and only
one) product category (let's call them Books and Guitars). Books have
different attributes than Guitars, and vice versa. I am trying to create a
set of tables so it can handle many different products and product types.
I know this has been done before (amazon.com, just as an example), but I
can't seem to figure out the best way to accomplish my goal.

What I have so far is the following: A Products table that contains:

ID
Description
Price
CategoryID

The CategoryID can be used as a foreign key to the Categories table, which
contains:

CategoryID
TableName

TableName is the name of a table which stores data that is specific to the
product type in question.

So, the Books table might look like this:

ID
BookName
ISBN
Author

And the Guitars table might look like this:

ID
Manufacturer
ModelName
ModelNumber
Color
Material
PickupType

Adding more products and product types would be fairly simple. My question
is: is there a more efficient way to store product information, and if so,
how?

I'm looking forward to hearing your comments/suggestions. Thanks in
advance, and happy new year to everyone!

-Philip
-Philip 

--
http://www.audiosurge.com   Capturing the Energy of Music
http://www.buymp3.com       The New Way to Buy Music
-
PHP3 Base Library Mailing List. Send messages to <phplib <email protected>>.
To unsubscribe, send "unsubscribe" to <phplib-request <email protected>> in
the body, not the subject, of your message.