Message-ID: <47DAABA8.60402@productivitymedia.com>
Date: Fri, 14 Mar 2008 12:45:28 -0400
From: PostgreSQL Admin <postgres@productivitymedia.com>
User-Agent: Thunderbird 2.0.0.12 (Macintosh/20080213)
MIME-Version: 1.0
To: PostgreSQL SQL List <pgsql-sql@postgresql.org>
Subject: DB Design
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I have a inventory system design in which I would like some help with to 
see if it's efficient.  The products are broken into:

Product tables
Property tables
Configurable Products - this would include colors (i.e. - black, blue 
and green) tied to products

I'm thinking of breaking inventory into 2 tables.

Product Inventory
Inventory Adjustment

Should I include the fk of the Configurable Product in the above tables 
or break it out further into more tables?

Product Inventory
Inventory Adjustment
--plus--
Product  Property Inventory
Inventory Property Adjustment

Thanks for any input,
J

These are my Product tables:

\d cart_product
                                       Table "public.cart_product"
      Column       |           Type           |                         
Modifiers                        
-------------------+--------------------------+-----------------------------------------------------------
 id                | integer                  | not null default 
nextval('cart_product_id_seq'::regclass)
 name              | character varying(128)   | not null
 kind              | character varying(40)    |
 sku               | character varying(15)    |
 short_description | character varying(255)   | not null
 description       | text                     |
 category_id       | integer                  | not null
 date_created      | timestamp with time zone | not null
 active            | boolean                  | not null
 in_stock          | boolean                  | not null
 featured          | boolean                  | not null
 ordering          | integer                  |
Indexes:
    "cart_product_pkey" PRIMARY KEY, btree (id)
    "cart_product_category_id" btree (category_id)
Foreign-key constraints:
    "cart_product_category_id_fkey" FOREIGN KEY (category_id) REFERENCES 
cart_category(id) DEFERRABLE INITIALLY DEFERRED


\d cart_propertyvariation
                                    Table "public.cart_propertyvariation"
    Column     |         Type          |                              
Modifiers                             
---------------+-----------------------+---------------------------------------------------------------------
 id            | integer               | not null default 
nextval('cart_propertyvariation_id_seq'::regclass)
 properties_id | integer               | not null
 name          | character varying(42) | not null
 value         | character varying(20) | not null
 order         | integer               |
Indexes:
    "cart_propertyvariation_pkey" PRIMARY KEY, btree (id)
    "cart_propertyvariation_properties_id" btree (properties_id)
Check constraints:
    "cart_propertyvariation_order_check" CHECK ("order" >= 0)
Foreign-key constraints:
    "properties_id_refs_id_73bc0a59" FOREIGN KEY (properties_id) 
REFERENCES cart_property(id) DEFERRABLE INITIALLY DEFERRED


\d cart_configurableproduct
                                Table "public.cart_configurableproduct"
     Column      |     Type     |                               
Modifiers                              
-----------------+--------------+-----------------------------------------------------------------------
 id              | integer      | not null default 
nextval('cart_configurableproduct_id_seq'::regclass)
 product_id      | integer      | not null
 variation_id    | integer      | not null
 price_change    | numeric(8,2) |
 weight_change   | integer      |
 quantity_change | integer      |
 active          | boolean      | not null
Indexes:
    "cart_configurableproduct_pkey" PRIMARY KEY, btree (id)
    "cart_configurableproduct_product_id" btree (product_id)
    "cart_configurableproduct_variation_id" btree (variation_id)
Foreign-key constraints:
    "cart_configurableproduct_product_id_fkey" FOREIGN KEY (product_id) 
REFERENCES cart_product(id) DEFERRABLE INITIALLY DEFERRED
    "cart_configurableproduct_variation_id_fkey" FOREIGN KEY 
(variation_id) REFERENCES cart_propertyvariation(id) DEFERRABLE 
INITIALLY DEFERRED