From: Troy <tjk@tksoft.com>
To: ries@jongert.nl
Cc: pgsql-sql@postgresql.org
Subject: Re: [OT] Inventory systems (private)
Date: Tue, 3 Dec 2002 06:46:46 -0800 (PST)
Message-ID: <200212031446.gB3Ekk6H010260@tksoft.com> (raw)
In-Reply-To: <000401c29ace$4c9d4170$f100000a@IT001>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tjk@tksoft.com, ries@jongert.nl
Subject: Re: [OT] Inventory systems (private)
In-Reply-To: <200212031446.gB3Ekk6H010260@tksoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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