From: PostgreSQL Admin <postgres@productivitymedia.com>
To: PostgreSQL SQL List <pgsql-sql@postgresql.org>
Subject: DB Design
Date: Fri, 14 Mar 2008 12:45:28 -0400
Message-ID: <47DAABA8.60402@productivitymedia.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgres@productivitymedia.com
Subject: Re: DB Design
In-Reply-To: <47DAABA8.60402@productivitymedia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox