pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed[OT] Inventory systems (private)
6+ messages / 4 participants
[nested] [flat]
* [OT] Inventory systems (private)
@ 2002-12-03 13:17 Ries van Twisk <ries@jongert.nl>
2002-12-03 14:46 ` Re: [OT] Inventory systems (private) Troy <tjk@tksoft.com>
2002-12-03 14:52 ` Re: [OT] Inventory systems (private) Andy Tawse <andy.t@newmediacollective.com>
2002-12-03 15:07 ` Re: [OT] Inventory systems (private) Dan MacNeil <omacneil@lctc.org>
0 siblings, 3 replies; 6+ messages in thread
From: Ries van Twisk @ 2002-12-03 13:17 UTC (permalink / raw)
To: pgsql-sql
Hi All,
is there any whitepaper, document or website that can point me to how to
setup a inventory system?
I'm particulary interested how other people solve the problem of a unknown
number of attributes to a inventory item.
example:
BAL <-- Inventory Item
- Color <- Attribute
- Diameter <- Attribute
- Weight <- Attribute
Car <-- Inventory Item
- Speed <- Attribute
- Size <- Attribute
- Weight <- Attribute
- Color <- Attribute
Computer <-- Inventory Item
- Brand <- Attribute
- Weight <- Attribute
- Windows/Linux <- Attribute
I can ofcource add any number of columns to a table but for a lot of items
there will be a lot of NULL values and currently I don't know how many
attrubutes one item can have (possible between 10 and 20). This can even
change in feature opon request.
Ries
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [OT] Inventory systems (private)
2002-12-03 13:17 [OT] Inventory systems (private) Ries van Twisk <ries@jongert.nl>
@ 2002-12-03 14:46 ` Troy <tjk@tksoft.com>
2002-12-03 15:06 ` Re: [OT] Inventory systems (private) Ries van Twisk <ries@jongert.nl>
2 siblings, 1 reply; 6+ messages in thread
From: Troy @ 2002-12-03 14:46 UTC (permalink / raw)
To: ries@jongert.nl; +Cc: pgsql-sql
Ries,
One solution is to create a table such as follows:
CREATE TABLE inventory (id serial, product text,
PRIMARY KEY (id)
) ;
CREATE TABLE attributes (prodid int4, textkey text, textvalue text, int4value int4,
FOREIGN KEY (prodid) REFERENCES inventory (id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE
);
INSERT INTO inventory (id, product) VALUES (100, 'Tire');
INSERT INTO attributes (prodid, textkey, textvalue) VALUES (100, 'Type', 'matchbox tire');
INSERT INTO attributes (prodid, textkey, textvalue) VALUES (100, 'Color', 'black');
INSERT INTO attributes (prodid, textkey, int4value, textvalue) VALUES (100, 'Diameter', 12, 'mm');
INSERT INTO attributes (prodid, textkey, int4value, textvalue) VALUES (100, 'Weight', 20, 'g');
CREATE INDEX textkeys ON attributes USING btree (prodid);
CREATE INDEX textkeys2 ON attributes USING btree (prodid,textkey);
To select diameter for product id 100 (Tire):
SELECT prodid FROM attributes WHERE prodid = 100 AND textkey = 'Diameter';
To select several:
SELECT prodid,textkey,textvalue,int4value FROM attributes WHERE prodid = 100 AND textkey IN ('Diameter', 'Weight', 'Color', 'Type');
and so on.
The indexes are just a fast guess. You would need to look at the queries
you generate and decide which indexes are needed.
Cheers,
Troy
Troy Korjuslommi Tksoft Inc.
tjk@tksoft.com
>
> Hi All,
>
> is there any whitepaper, document or website that can point me to how to
> setup a inventory system?
> I'm particulary interested how other people solve the problem of a unknown
> number of attributes to a inventory item.
>
> example:
> BAL <-- Inventory Item
> - Color <- Attribute
> - Diameter <- Attribute
> - Weight <- Attribute
>
> Car <-- Inventory Item
> - Speed <- Attribute
> - Size <- Attribute
> - Weight <- Attribute
> - Color <- Attribute
>
> Computer <-- Inventory Item
> - Brand <- Attribute
> - Weight <- Attribute
> - Windows/Linux <- Attribute
>
>
> I can ofcource add any number of columns to a table but for a lot of items
> there will be a lot of NULL values and currently I don't know how many
> attrubutes one item can have (possible between 10 and 20). This can even
> change in feature opon request.
>
> Ries
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [OT] Inventory systems (private)
2002-12-03 13:17 [OT] Inventory systems (private) Ries van Twisk <ries@jongert.nl>
2002-12-03 14:46 ` Re: [OT] Inventory systems (private) Troy <tjk@tksoft.com>
@ 2002-12-03 15:06 ` Ries van Twisk <ries@jongert.nl>
2002-12-09 21:01 ` Re: [OT] Inventory systems (private) Troy <tjk@tksoft.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ries van Twisk @ 2002-12-03 15:06 UTC (permalink / raw)
To: pgsql-sql
Troy, Andy,
you both are right and it was my first tought. My only concern is that would
this system be fast enough for large tables (for me large is around 250.000
unique items) and thus my attribute table would be around 2.500.000 and
5.000.000 entrys. A record for one attribute is small I think around 128
byte in size.
One thing is that every attribute must be in it's own domain. For weight for
example I have three different meanings ( 1] Pull weight 2] push weight 3]
weight of the items itself) but using the method troy suggested that would
not be a problem if I create some sort of a domain table.
Anyway so far thangs for the quick responses, I've got something to work on.
best regards,
Ries van Twisk
-----Oorspronkelijk bericht-----
Van: Troy [mailto:tjk@tksoft.com]
Verzonden: dinsdag 3 december 2002 15:47
Aan: Ries van Twisk
CC: pgsql-sql@postgresql.org
Onderwerp: Re: [SQL] [OT] Inventory systems (private)
Ries,
One solution is to create a table such as follows:
CREATE TABLE inventory (id serial, product text,
PRIMARY KEY (id)
) ;
CREATE TABLE attributes (prodid int4, textkey text, textvalue text,
int4value int4,
FOREIGN KEY (prodid) REFERENCES inventory (id) MATCH FULL ON DELETE CASCADE
ON UPDATE CASCADE
);
INSERT INTO inventory (id, product) VALUES (100, 'Tire');
INSERT INTO attributes (prodid, textkey, textvalue) VALUES (100, 'Type',
'matchbox tire');
INSERT INTO attributes (prodid, textkey, textvalue) VALUES (100, 'Color',
'black');
INSERT INTO attributes (prodid, textkey, int4value, textvalue) VALUES (100,
'Diameter', 12, 'mm');
INSERT INTO attributes (prodid, textkey, int4value, textvalue) VALUES (100,
'Weight', 20, 'g');
CREATE INDEX textkeys ON attributes USING btree (prodid);
CREATE INDEX textkeys2 ON attributes USING btree (prodid,textkey);
To select diameter for product id 100 (Tire):
SELECT prodid FROM attributes WHERE prodid = 100 AND textkey = 'Diameter';
To select several:
SELECT prodid,textkey,textvalue,int4value FROM attributes WHERE prodid = 100
AND textkey IN ('Diameter', 'Weight', 'Color', 'Type');
and so on.
The indexes are just a fast guess. You would need to look at the queries
you generate and decide which indexes are needed.
Cheers,
Troy
Troy Korjuslommi Tksoft Inc.
tjk@tksoft.com
>
> Hi All,
>
> is there any whitepaper, document or website that can point me to how to
> setup a inventory system?
> I'm particulary interested how other people solve the problem of a unknown
> number of attributes to a inventory item.
>
> example:
> BAL <-- Inventory Item
> - Color <- Attribute
> - Diameter <- Attribute
> - Weight <- Attribute
>
> Car <-- Inventory Item
> - Speed <- Attribute
> - Size <- Attribute
> - Weight <- Attribute
> - Color <- Attribute
>
> Computer <-- Inventory Item
> - Brand <- Attribute
> - Weight <- Attribute
> - Windows/Linux <- Attribute
>
>
> I can ofcource add any number of columns to a table but for a lot of items
> there will be a lot of NULL values and currently I don't know how many
> attrubutes one item can have (possible between 10 and 20). This can even
> change in feature opon request.
>
> Ries
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [OT] Inventory systems (private)
2002-12-03 13:17 [OT] Inventory systems (private) Ries van Twisk <ries@jongert.nl>
2002-12-03 14:46 ` Re: [OT] Inventory systems (private) Troy <tjk@tksoft.com>
2002-12-03 15:06 ` Re: [OT] Inventory systems (private) Ries van Twisk <ries@jongert.nl>
@ 2002-12-09 21:01 ` Troy <tjk@tksoft.com>
0 siblings, 0 replies; 6+ messages in thread
From: Troy @ 2002-12-09 21:01 UTC (permalink / raw)
To: ries@jongert.nl; +Cc: pgsql-sql
Ries,
Sorry I missed a few days here. I was busy elsewhere.
Anyway, the speed issue is fixed with indexes. Once you know
the kind of queries you will be making, create an optimized index for
each one of those queries.
You probably want to have a separate table for storing
the attribute names. I.e. instead of using a text variable
in each record, use a unique id which references an entry
in an attributes table. This won't affect the speed of the
system but it will save some space. Or if nothing else, it will
be more elegant.
Cheers,
Troy
>
> Troy, Andy,
>
> you both are right and it was my first tought. My only concern is that would
> this system be fast enough for large tables (for me large is around 250.000
> unique items) and thus my attribute table would be around 2.500.000 and
> 5.000.000 entrys. A record for one attribute is small I think around 128
> byte in size.
>
> One thing is that every attribute must be in it's own domain. For weight for
> example I have three different meanings ( 1] Pull weight 2] push weight 3]
> weight of the items itself) but using the method troy suggested that would
> not be a problem if I create some sort of a domain table.
>
> Anyway so far thangs for the quick responses, I've got something to work on.
>
> best regards,
> Ries van Twisk
>
>
>
>
> -----Oorspronkelijk bericht-----
> Van: Troy [mailto:tjk@tksoft.com]
> Verzonden: dinsdag 3 december 2002 15:47
> Aan: Ries van Twisk
> CC: pgsql-sql@postgresql.org
> Onderwerp: Re: [SQL] [OT] Inventory systems (private)
>
>
> Ries,
>
> One solution is to create a table such as follows:
>
> CREATE TABLE inventory (id serial, product text,
> PRIMARY KEY (id)
> ) ;
> CREATE TABLE attributes (prodid int4, textkey text, textvalue text,
> int4value int4,
> FOREIGN KEY (prodid) REFERENCES inventory (id) MATCH FULL ON DELETE CASCADE
> ON UPDATE CASCADE
> );
>
> INSERT INTO inventory (id, product) VALUES (100, 'Tire');
>
> INSERT INTO attributes (prodid, textkey, textvalue) VALUES (100, 'Type',
> 'matchbox tire');
> INSERT INTO attributes (prodid, textkey, textvalue) VALUES (100, 'Color',
> 'black');
> INSERT INTO attributes (prodid, textkey, int4value, textvalue) VALUES (100,
> 'Diameter', 12, 'mm');
> INSERT INTO attributes (prodid, textkey, int4value, textvalue) VALUES (100,
> 'Weight', 20, 'g');
> CREATE INDEX textkeys ON attributes USING btree (prodid);
> CREATE INDEX textkeys2 ON attributes USING btree (prodid,textkey);
>
> To select diameter for product id 100 (Tire):
> SELECT prodid FROM attributes WHERE prodid = 100 AND textkey = 'Diameter';
>
> To select several:
> SELECT prodid,textkey,textvalue,int4value FROM attributes WHERE prodid = 100
> AND textkey IN ('Diameter', 'Weight', 'Color', 'Type');
>
>
> and so on.
>
> The indexes are just a fast guess. You would need to look at the queries
> you generate and decide which indexes are needed.
>
>
>
> Cheers,
>
>
> Troy
>
> Troy Korjuslommi Tksoft Inc.
> tjk@tksoft.com
>
>
>
>
> >
> > Hi All,
> >
> > is there any whitepaper, document or website that can point me to how to
> > setup a inventory system?
> > I'm particulary interested how other people solve the problem of a unknown
> > number of attributes to a inventory item.
> >
> > example:
> > BAL <-- Inventory Item
> > - Color <- Attribute
> > - Diameter <- Attribute
> > - Weight <- Attribute
> >
> > Car <-- Inventory Item
> > - Speed <- Attribute
> > - Size <- Attribute
> > - Weight <- Attribute
> > - Color <- Attribute
> >
> > Computer <-- Inventory Item
> > - Brand <- Attribute
> > - Weight <- Attribute
> > - Windows/Linux <- Attribute
> >
> >
> > I can ofcource add any number of columns to a table but for a lot of items
> > there will be a lot of NULL values and currently I don't know how many
> > attrubutes one item can have (possible between 10 and 20). This can even
> > change in feature opon request.
> >
> > Ries
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [OT] Inventory systems (private)
2002-12-03 13:17 [OT] Inventory systems (private) Ries van Twisk <ries@jongert.nl>
@ 2002-12-03 14:52 ` Andy Tawse <andy.t@newmediacollective.com>
2 siblings, 0 replies; 6+ messages in thread
From: Andy Tawse @ 2002-12-03 14:52 UTC (permalink / raw)
To: pgsql-sql
While I'm not expert what I would do is something like this
Items table
fields: item_id item_name
Attributes table
fields: attribute_id attribute_name
Item_Attr table
fields: item_id attribute_id attribute_value
Now an item can be associated with any number of attributes.
03/12/2002 13:17:15, "Ries van Twisk" <ries@jongert.nl> wrote:
>Hi All,
>
>is there any whitepaper, document or website that can point me to how to
>setup a inventory system?
>I'm particulary interested how other people solve the problem of a unknown
>number of attributes to a inventory item.
>
>example:
>BAL <-- Inventory Item
> - Color <- Attribute
> - Diameter <- Attribute
> - Weight <- Attribute
>
>Car <-- Inventory Item
> - Speed <- Attribute
> - Size <- Attribute
> - Weight <- Attribute
> - Color <- Attribute
>
>Computer <-- Inventory Item
> - Brand <- Attribute
> - Weight <- Attribute
> - Windows/Linux <- Attribute
>
>
>I can ofcource add any number of columns to a table but for a lot of items
>there will be a lot of NULL values and currently I don't know how many
>attrubutes one item can have (possible between 10 and 20). This can even
>change in feature opon request.
>
>Ries
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [OT] Inventory systems (private)
2002-12-03 13:17 [OT] Inventory systems (private) Ries van Twisk <ries@jongert.nl>
@ 2002-12-03 15:07 ` Dan MacNeil <omacneil@lctc.org>
2 siblings, 0 replies; 6+ messages in thread
From: Dan MacNeil @ 2002-12-03 15:07 UTC (permalink / raw)
To: Ries van Twisk <ries@jongert.nl>; +Cc: pgsql-sql
You might create a seperate attribute tables.
table: items
id
name
table: attributes
id
description
table: item_attributes
item_id
attribute_id
SELECT
items.name, attributes.description
FROM
items, attributes, item_attributes
WHERE
items.id=item_attributes.item_id
AND item_attributes.item_id = attributes.id;
If some items are also attributes of other items (a car has an engine) you
might look at tree structures.
Of course there are already existing inventory systems that it might be
cheaper to use than to grow your own.
On Tue, 3 Dec 2002, Ries van Twisk wrote:
> Hi All,
>
> is there any whitepaper, document or website that can point me to how to
> setup a inventory system?
> I'm particulary interested how other people solve the problem of a unknown
> number of attributes to a inventory item.
>
> example:
> BAL <-- Inventory Item
> - Color <- Attribute
> - Diameter <- Attribute
> - Weight <- Attribute
>
> Car <-- Inventory Item
> - Speed <- Attribute
> - Size <- Attribute
> - Weight <- Attribute
> - Color <- Attribute
>
> Computer <-- Inventory Item
> - Brand <- Attribute
> - Weight <- Attribute
> - Windows/Linux <- Attribute
>
>
> I can ofcource add any number of columns to a table but for a lot of items
> there will be a lot of NULL values and currently I don't know how many
> attrubutes one item can have (possible between 10 and 20). This can even
> change in feature opon request.
>
> Ries
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2002-12-09 21:01 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-12-03 13:17 [OT] Inventory systems (private) Ries van Twisk <ries@jongert.nl>
2002-12-03 14:46 ` Troy <tjk@tksoft.com>
2002-12-03 15:06 ` Ries van Twisk <ries@jongert.nl>
2002-12-09 21:01 ` Troy <tjk@tksoft.com>
2002-12-03 14:52 ` Andy Tawse <andy.t@newmediacollective.com>
2002-12-03 15:07 ` Dan MacNeil <omacneil@lctc.org>
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