DB Design

From: PostgreSQL Admin <postgres(at)productivitymedia(dot)com>
To: PostgreSQL SQL List <pgsql-sql(at)postgresql(dot)org>
Subject: DB Design
Date: 2008-03-14 16:45:28
Message-ID: 47DAABA8.60402@productivitymedia.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Browse pgsql-sql by date

  From Date Subject
Next Message Aarni Ruuhimäki 2008-03-14 17:25:26 Re: Counting days ...
Previous Message Frank Bax 2008-03-14 16:09:20 Re: Counting days ...