Thread: database design of products catalogue

database design of products catalogue

From
Terence Ng
Date:
Hi!

I would like to create a database for products
catalogue.  I have many products with different
specification.

e.g.
lamp A: Part no, Color, Raw Material, Wave length,
Vf(V)20mA, lv(ucd)10mA

ECG:Part no, Type, Iv, KHz, A, W, Im

Should I put these products in one table, or in
separate?

Best regards,
Terence

_______________________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com.hk address at http://mail.english.yahoo.com.hk

Re: database design of products catalogue

From
Tino Wildenhain
Date:
Hi,

Terence Ng wrote:
> Hi!
>
> I would like to create a database for products
> catalogue.  I have many products with different
> specification.
>
> e.g.
> lamp A: Part no, Color, Raw Material, Wave length,
> Vf(V)20mA, lv(ucd)10mA
>
> ECG:Part no, Type, Iv, KHz, A, W, Im
>
> Should I put these products in one table, or in
> separate?

Several posibilities. One I'd give a go
would be
one table for products and their common data

products (products_id int8, description text, ...);

attributenames (attr_id int4, importance int2, attrname text (or
attrname int4 pointing to translation table)
,unit_id)

attributeunits (unit_id int4, unitname text, baseunit_id int4, factor
numeric)

attributes (product_id int8, attr_id int4, unit_id int4, attrtxt text,
attrvalue numeric)


I think you get the picture :-)

Good luck
Tino Wildenhain



Re: database design of products catalogue

From
Ron Johnson
Date:
On Mon, 2003-07-21 at 07:04, Terence Ng wrote:
> Hi!
>
> I would like to create a database for products
> catalogue.  I have many products with different
> specification.
>
> e.g.
> lamp A: Part no, Color, Raw Material, Wave length,
> Vf(V)20mA, lv(ucd)10mA
>
> ECG:Part no, Type, Iv, KHz, A, W, Im
>
> Should I put these products in one table, or in
> separate?

You need a good book/web site on database design and normalization.

*Basically*, though, every item that describes a product should go
in the "product" table.  Then, every different color (and, say,
whether it pertains to "lamp" or "desk") should go in a "color"
table.  Likewise with "raw_material", "wavelength" and "voltage"
tables.

Then, using Foreign Keys, you can link the product table to the
static fact tables to ensure the integrity of the data.

--
+-----------------------------------------------------------------+
| Ron Johnson, Jr.        Home: ron.l.johnson@cox.net             |
| Jefferson, LA  USA                                              |
|                                                                 |
| "I'm not a vegetarian because I love animals, I'm a vegetarian  |
|  because I hate vegetables!"                                    |
|    unknown                                                      |
+-----------------------------------------------------------------+



Re: database design of products catalogue

From
Paul Thomas
Date:
On 21/07/2003 13:04 Terence Ng wrote:
> Hi!
>
> I would like to create a database for products
> catalogue.  I have many products with different
> specification.
>
> e.g.
> lamp A: Part no, Color, Raw Material, Wave length,
> Vf(V)20mA, lv(ucd)10mA
>
> ECG:Part no, Type, Iv, KHz, A, W, Im
>
> Should I put these products in one table, or in
> separate?
>

You might be able to do it as one table. Whether that would be the best
design, I cannot say. Personally I would approach the problem from 2
different angles.

Firstly I would would list all the possible attributes a part could
possibly have. For parts which don't possess a particular attribute, store
a null in that column. Draw up a grid or spreadsheet of columns/parts and
put an X in every column which would be null for a particular part. If you
get lots of X's then a single table might not be the best solution.

Secondly, look at the application from the user's perspective (the use
cases). What attributes are to be displayed for different parts? Are they
all similar? Do the screens make separate tables seem more natural?


HTH


--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+