Database design for multiple types of products

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

I need to create an application that handles several product types/
categories. Each product type can have totally different fields to
describe it. For instance a car would have Year, Make, Model, and
Miles, whereas a house would have Year, Address, SQ Feet, Amenities,
These are just examples but you get my drift.

the application needs a form to input, show and search the data for
each of the product types.

I cannot hard-code each product type and its associated fields in the
database, since one of the requirements is that an administrative user
can create any product type he/she needs. The application then has to
generate the forms for data input, show, and search on the main site.

What are your suggestions as how to best address these requirements?
What would be the best DB schema?


Re: Database design for multiple types of products

MadDiver contained the following:

Quoted text here. Click to load it

It sounds like you are going to need a separate table for each type of
product.  The downside of this is that you would be letting the client
design the database and they could include non-key dependent attributes

Year, Address, SQ Feet, Amenities, water co, water co tel, water co

Furthermore they would probably assign incorrect data types.
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs

Re: Database design for multiple types of products

MadDiver says...

Quoted text here. Click to load it

The following basic design will achieve what you want, but it is an  
inefficient way of doing things as you have to store all attribute values  
in a (largish) character field, even if they are numbers/dates/etc.

But if you must, then ....

- primary key PT_ID
1      Car
2      House

- primary key PTA_ID, foreign key PT_ID
1       1      1           Year
2       1      2           Make
3       1      3           Model
7       2      1           Year Built
8       2      2           Street Address
9       2      3           Town/City

Table of PRODUCT  
- composite primary key on P_ID, PTA_ID, foreign key PT_ID, depending on  
what database a constraint based on PT_ID/PTA_ID would be good
88    1      2        Porsche
88    1      3        Carerra
88    1      1        2006
93    2      7        1980
93    2      9        Nerdsville
93    2      8        10 Somewhere Street

If you want to make some fields mandatory/optional, add another flag  
column to the PRODUCT_TYPE_ATTRIBUTE table.


Re: Database design for multiple types of products

Kimmo Laine wrote:
Quoted text here. Click to load it

Unless next_page.php generates PHP, the script with this include will
only get HTML.

Quoted text here. Click to load it


    if (isset($_GET['foo'])) {
      echo '<?php echo $_GET[\'foo\']; ?>';
    } else {
      echo '<?php echo \'Not available\'; ?>';

File not found: (R)esume, (R)etry, (R)erun, (R)eturn, (R)eboot

Re: Database design for multiple types of products

Quoted text here. Click to load it

Giving less savvy users control over database schemes is somewhat tricky...

If they are totally independant, I'd say you've got your work cut out for  

Not a terribly good implementation, but one that would be somewhat  

- Define some types of properties (Date, Number, Longtext, varchar etc.)
- Define some names templates for types (Car as varchar modelname, number  
miles, date buildyear etc.)
- Have one major table having at least an ID (and every other field all  
products will have in common.
- Have a properties table with property-id, product-id and the  
- Build forms & interfaces using that.

It gives you major flexibility, but it would mean the data of of your  
'products' is scattered over several tables/rows. Not an ideal solution by  
far, and I wouldn't even try it without cascading updates & deletes.

Rik Wasmus

Re: Database design for multiple types of products

MadDiver wrote:
Quoted text here. Click to load it

You *can* set up your "reference" table so that it not only has a  
foreign key of the index into the other table, but also names that  
"other" table.

I don't think this falls within the normally expected use of the term  
"relational database", but can certainly work, and needn't be slow, and  
may be a tidier solution than trying to "rationalise" widely varying  
types of data, such as the ones you're describing.


Site Timeline