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 nullkind |
charactervarying(40) |sku | character varying(15) |short_description | character varying(255) |
notnulldescription | text |category_id | integer | not
nulldate_created | timestamp with time zone | not nullactive | boolean | not
nullin_stock | boolean | not nullfeatured | boolean | not
nullordering | 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 nullname |
charactervarying(42) | not nullvalue | character varying(20) | not nullorder | 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 nullvariation_id | integer
| not nullprice_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