Re: [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
>