another approach would be:
table product:
productid int8 PK
productname charvar(255)
table versions
productid int8 FK
versionid int8 PK
size
color
...
quantity int4
an example would be then:
table product:
- productid: 123, productname: 'nice cotton t-shirt'
- productid: 442, productname: 'another cotton t-shirt'
table versions:
- productid: 123, versionid: 1, color: 'black', size: 'all', quantity: 11
- productid: 442, versionid: 2, color: 'yellow', size: 'l', quantity: 1
- productid: 442, versionid: 2, color: 'yellow', size: 's', quantity: 4
- productid: 442, versionid: 2, color: 'red', size: 'xl', quantity: 9
- productid: 442, versionid: 2, color: 'blue', size: 's', quantity: 0
that way you can have more than 1 quantity / color / size combination per
product and still have products that come in one size. so instead of only
using a 2nd table for cases where more than one size is available, you would
always use a 2nd table. this probably reduces your code complexity quite a
bit and only needs 1 JOIN.
- thomas
----- Original Message -----
From: "Patrick Hatcher" <[EMAIL PROTECTED]>
To: "NbForYou" <[EMAIL PROTECTED]>
Cc: <pgsql-performance@postgresql.org>;
<[EMAIL PROTECTED]>
Sent: Sunday, March 19, 2006 2:59 PM
Subject: Re: [PERFORM] database model tshirt sizes
We have size and color in the product table itself. It is really an
attribute of the product. If you update the availability of the product
often, I would split out the quantity into a separate table so that you
can
truncate and update as needed.
Patrick Hatcher
Development Manager Analytics/MIO
Macys.com
"NbForYou"
<[EMAIL PROTECTED]
.com> To
Sent by: <pgsql-performance@postgresql.org>
pgsql-performance cc
[EMAIL PROTECTED]
.org Subject
[PERFORM] database model tshirt
sizes
03/18/06 07:03 AM
Hello,
Does anybody know how to build a database model to include sizes for
rings,
tshirts, etc?
the current database is built like:
table product
=========
productid int8 PK
productname charvar(255)
quantity int4
what i want now is that WHEN (not all products have multiple sizes) there
are multiple sizes available. The sizes are stored into the database. I
was
wondering to include a extra table:
table sizes:
========
productid int8 FK
size varchar(100)
but then i have a quantity problem. Because now not all size quantities
can
be stored into this table, because it allready exist in my product table.
How do professionals do it? How do they make their model to include sizes
if any available?
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
