Database design for multiple types of products

[MadDiver]

Guys,

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,
etc.
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?

THANKS!!!

[Geoff Berrow]

Re: Database design for multiple types of products

Message-ID: <1181019367.152 432.299980@m36g 2000hse.googleg roups.comfrom
MadDiver contained the following:

>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,
>etc.

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

eg
House
Year, Address, SQ Feet, Amenities, water co, water co tel, water co
address

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 http://www.ckdog.co.uk/rfdmaker/

[Geoff Muldoon]

Re: Database design for multiple types of products

MadDiver says...

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,
etc.

What would be the best DB schema?

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 ....

Table of PRODUCT_TYPE
- primary key PT_ID
PT_ID PT_DESC
1 Car
2 House

Table of PRODUCT_TYPE_AT TRIBUTE
- primary key PTA_ID, foreign key PT_ID
PTA_ID PT_ID SORT_ORDER PTA_DESC
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
P_ID PT_ID PTA_ID P_VALUE
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_AT TRIBUTE table.

GM

[Rik]

Re: Database design for multiple types of products

On Tue, 05 Jun 2007 06:56:07 +0200, MadDiver <paixaop@gmail. comwrote:

Guys,
>
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,
etc.
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?

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
you.

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

- 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
property-value.
- 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

[Usenet]

Re: Database design for multiple types of products

In article <1181019367.152 432.299980@m36g 2000hse.googleg roups.com>,
MadDiver wrote:

I need to create an application that handles several product types/

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 "rationalis e" widely varying
types of data, such as the ones you're describing.

Mark

[Toby A Inkster]

Re: Database design for multiple types of products

Geoff Muldoon wrote:

But if you must, then ....

This database schema is sound overall, but I'd consider extending it
slightly.

Firstly, some attributes are probably going to be common amongst
all of the different types of product -- things like Name, Price,
and Stock_Quantity.

Other attributes are going to be shared by a few different types of
products, but not all of them. Examples might be Height, Width,
Depth, Colour and so on.

I'd start with a main table of all products including a product ID, a
product type and any of those first set of core attributes.

=============== =============== =============== ====
PRODUCTS
-------------------------------------------------
ID Type Name Price Qty
-------------------------------------------------
1 CD The White Album £8.99 12
2 CD Play £7.99 8
3 PNTNG Mona Lisa (NULL) 0
4 CAR Corsa £5999.00 3
5 CAR Previa £7499.99 2
6 BOOK Bleak House £3.99 7
7 BOOK Lucky Jim £4.99 3
8 BOOK Foucault's Pendulum £5.99 2
=============== =============== =============== ====

As I said, there might be other "core attributes" in there.

Now, a table listing all our different types of types.

=============== =============== =============== ====
TYPES
-----------------------------------------------
Type Type_Desc Type_Desc_Plura l
-----------------------------------------------
CD Compact Disc Compact Discs
PNTNG Work of Art Works of Art
CAR Car Cars
BOOK Book Books
SALAD Salad Salads
DVD DVD DVDs
F Fish Fish
=============== =============== =============== ====

I've included a "Type_Desc_Plur al" column in there which is not entirely
frivolous -- as you can see, the rule of simply adding an 's' to the
singular doesn't always work!

Now we set up a bunch of additional attributes.

=============== =============== =============== ====
ATTRIBUTES
-----------------------------------------------
Attribute Attribute_Desc
-----------------------------------------------
COL Colour
H Height
W Width
D Depth
ENGINE Engine size
SPEC Species
BY Maker
=============== =============== =============== ====

Now here's an interesting one -- I could have defined "painter", "singer"
and "manufactur er" attributes separately, but I want to allow the
application to realise that these all mean the same thing, so I've just
defined "maker". This allows you to search for all items where the "maker"
is "Joe Bloggs" rather than having to search individually through
paintings, CDs, cars and so forth.

In the next table, we define which attributes are relevant to which type
of product, and also define a more specific Attribute_Desc.

=============== =============== =============== ====
ATTRIBUTE_APPLI CATION
-----------------------------------------------
Attribute Type Label
-----------------------------------------------
COL CAR Paintwork
H CAR Height
W CAR Width
D CAR Length
ENGINE CAR Engine size
SPEC F Species
COL F (NULL)
COL CD Casing Colour
BY CAR Manufacturer
BY PNTNG Artist
BY CD Singer/Artist
BY DVD Director
BY BOOK Author
=============== =============== =============== ====

Finally, we fill in the detailed information:

=============== =============== =============== ====
PRODUCT_ATTRIBU TES
-------------------------------------------------
ID Attribute Value
-------------------------------------------------
1 BY The Beatles
1 COL White
2 BY Moby
2 COL Blue
3 BY Leonardo Da Vinci
4 COL Blue
4 BY Vauxhall
6 BY Charles Dickens
7 BY Kingsley Amis
8 BY Umberto Eco
=============== =============== =============== ====

So now doing an inner join on products, product_attribu tes and
attribute_appli cation and searching for blue things, will tell you that
the Corsa's paint work is blue, and Moby's casing is blue.

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.12-12mdksmp, up 102 days, 1:31.]

URLs in demiblog

http://tobyinkster.co.uk/blog/2007/05/31/demiblog-urls/