Message-Id: <200212031446.gB3Ekk6H010260@tksoft.com>
Subject: Re: [OT] Inventory systems    (private)
To: ries@jongert.nl (Ries van Twisk)
Date: Tue, 3 Dec 2002 06:46:46 -0800 (PST)
Cc: pgsql-sql@postgresql.org
Reply-To: tjk@tksoft.com (Troy K.), <tjk@tksoft.com>
In-Reply-To: <000401c29ace$4c9d4170$f100000a@IT001> from "Ries van Twisk" at
	Dec 03, 2002 02:17:15 PM
From: "Troy" <tjk@tksoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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
>