pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feeddatabase model tshirt sizes
4+ messages / 3 participants
[nested] [flat]
* database model tshirt sizes
@ 2006-03-18 15:03 NbForYou <nbforyou@hotmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: NbForYou @ 2006-03-18 15:03 UTC (permalink / raw)
To: pgsql-performance
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: database model tshirt sizes
@ 2006-03-19 13:59 Patrick Hatcher <PHatcher@macys.com>
parent: NbForYou <nbforyou@hotmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Patrick Hatcher @ 2006-03-19 13:59 UTC (permalink / raw)
To: NbForYou <nbforyou@hotmail.com>; +Cc: pgsql-performance; pgsql-performance-owner@postgresql.org
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"
<nbforyou@hotmail
.com> To
Sent by: <pgsql-performance@postgresql.org>
pgsql-performance cc
-owner@postgresql
.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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: database model tshirt sizes
@ 2006-03-19 14:37 me@alternize.com
parent: Patrick Hatcher <PHatcher@macys.com>
0 siblings, 1 reply; 4+ messages in thread
From: me@alternize.com @ 2006-03-19 14:37 UTC (permalink / raw)
To: NbForYou <nbforyou@hotmail.com>; +Cc: pgsql-performance; pgsql-performance-owner@postgresql.org
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" <PHatcher@macys.com>
To: "NbForYou" <nbforyou@hotmail.com>
Cc: <pgsql-performance@postgresql.org>;
<pgsql-performance-owner@postgresql.org>
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"
> <nbforyou@hotmail
> .com> To
> Sent by: <pgsql-performance@postgresql.org>
> pgsql-performance cc
> -owner@postgresql
> .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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: database model tshirt sizes
@ 2006-03-19 17:43 NbForYou <nbforyou@hotmail.com>
parent: me@alternize.com
0 siblings, 0 replies; 4+ messages in thread
From: NbForYou @ 2006-03-19 17:43 UTC (permalink / raw)
To: me@alternize.com; +Cc: pgsql-performance; PHatcher@macys.com
So a default value for all products would be size:"all"
for example, the same tshirt shop also sells cdroms.
It size attribute would be to place it to be :"all". (because we cannot
place an uniqe index on null values)
But the industry evolves and so in time the same cdrom is now available for
pc and playstation.
So i would like to have it as 1 productid but with different attributes: pc
(with quantity 5) and playstation (with quantity 3).
So when I do an insert for this 2 products with 1 productid it would be
like:
insert into versions (productid,size,quantity) values (345,'pc',5);
insert into versions (productid,size,quantity) values (345,'playstation',3);
if however the product existed we get an error:
because the default value version "all" did also exist and is now obsolete
population 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
productid: 345, versionid: 3, color: null, size: 'all', quantity: 15
productid: 345, versionid: 3, color: null, size: 'pc', quantity: 5
productid: 345, versionid: 3, color: null, size: 'playstation', quantity: 3
WOULD HAVE TO BE:
population 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
productid: 345, versionid: 3, color: null, size: 'pc', quantity: 5
productid: 345, versionid: 3, color: null, size: 'playstation', quantity: 3
ALSO:
what is versionid used for?
----- Original Message -----
From: <me@alternize.com>
To: "NbForYou" <nbforyou@hotmail.com>
Cc: <pgsql-performance@postgresql.org>;
<pgsql-performance-owner@postgresql.org>
Sent: Sunday, March 19, 2006 3:37 PM
Subject: Re: [PERFORM] database model tshirt sizes
> 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" <PHatcher@macys.com>
> To: "NbForYou" <nbforyou@hotmail.com>
> Cc: <pgsql-performance@postgresql.org>;
> <pgsql-performance-owner@postgresql.org>
> 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"
>> <nbforyou@hotmail
>> .com> To
>> Sent by: <pgsql-performance@postgresql.org>
>> pgsql-performance cc
>> -owner@postgresql
>> .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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-03-19 17:43 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-18 15:03 database model tshirt sizes NbForYou <nbforyou@hotmail.com>
2006-03-19 13:59 ` Patrick Hatcher <PHatcher@macys.com>
2006-03-19 14:37 ` me@alternize.com
2006-03-19 17:43 ` NbForYou <nbforyou@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox