473,323 Members | 1,547 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Database design for multiple types of products

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

Jun 5 '07 #1
5 5251
Message-ID: <11**********************@m36g2000hse.googlegroups .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/
Jun 5 '07 #2
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_ATTRIBUTE
- 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_ATTRIBUTE table.

GM
Jun 5 '07 #3
Rik
On Tue, 05 Jun 2007 06:56:07 +0200, MadDiver <pa*****@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
Jun 5 '07 #4
In article <11**********************@m36g2000hse.googlegroups .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 "rationalise" widely varying
types of data, such as the ones you're describing.

Mark

Jun 5 '07 #5
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_Plural
-----------------------------------------------
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_Plural" 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 "manufacturer" 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_APPLICATION
-----------------------------------------------
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_ATTRIBUTES
-------------------------------------------------
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_attributes and
attribute_application 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/
Jun 5 '07 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: _Skare_Krow_ | last post by:
I've been working on a project for awhile now and can't seem to get it working the way I want. What I've got is a database with different category's and products below them. The thing I'm having...
346
by: rkusenet | last post by:
http://biz.yahoo.com/rc/040526/tech_database_marketshare_1.html Interesting to see that database sales for windows is more than Unix.
19
by: Steve Jorgensen | last post by:
I've run across this issue several times of late, and I've never come up with a satisfactory answer to the best way to handle this schema issue. You have a large section of schema in which a...
7
by: serge | last post by:
How can I run a single SP by asking multiple sales question either by using the logical operator AND for all the questions; or using the logical operator OR for all the questions. So it's always...
29
by: MP | last post by:
Greets, context: vb6/ado/.mdb/jet 4.0 (no access)/sql beginning learner, first database, planning stages (I think the underlying question here is whether to normalize or not to normalize this...
0
by: YellowFin Announcements | last post by:
Introduction Usability and relevance have been identified as the major factors preventing mass adoption of Business Intelligence applications. What we have today are traditional BI tools that...
4
by: beatdream | last post by:
I am designing a database to handle different kinds of products ... and these products can have different properties...for example, a trouser can be specified by the width, length, color, and other...
8
by: morleyc | last post by:
Hi, until recently i was quite happy to add data sources from mssql database in visual studio and drag the datasets directly onto the form this creating a directly editable form which worked well....
9
by: Peter Duniho | last post by:
Is there a straightfoward API in .NET that allows for inspection of a database? That is, to look at the structure of the database, without knowing anything in advance about it? For example,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.