Product DB

[John_Blaze]

I have a DB to setup using products:

This is what I have and am seeking input and design for Inventory. I want to separate inventory by product properties (i.e. Sizes [Small - Medium - Large - X-large ] Color [White - Black - Green] )

So, count by color, by color & size (etc.)

Products
- ProductID
- Name
- Description
- CategoryID

ProductPrice
-PriceID
-ProductID
-Price

ProductProperty
-ID
-Name
-Desc
-Order

PropertyVariation
-ID
-PropertyID
-Name
-Value

I am thinking about breaking out inventory into 2 tables:
Inventory
-ID
-ProductID
-Quantity
-Current Quantity
-Order

PropertyInventory
-ID
-InventoryID
-PropertyID
-Quantity
-CurrentQuantity

Does this sound about right or should it be simplified?

Thanks for any input.
J

[trudge]

Your tables suggest you may need to do some more work on RDBM design. You have features and attributes of Products spread throughout several tables, when I believe they could all be in one table, with the appropriate fields. You want to try and collect all information about a thing or event in one table. In your case, so far I've only seen mention of 'Products'. What else are you going to put in your database, if anything?

Try and get a good book on relational database design. Some good ones that I use are 'Database Design for Mere Mortals' by Michael Hernandez, 'SQL Queries for Mere Mortals' by same author, and 'MySQL' by Paul Dubois.

[John_Blaze]

I think that the tables are spread to thin. I need pair them down abit, I just want product inventory system that tells you how many red t-shirts in xlarge without have a column (red shirt - xl).

Something for quick look ups - shirts then more tables (I'm thinking) once a user drives down the search.

[trudge]

I think that the tables are spread to thin. I need pair them down abit, I just want product inventory system that tells you how many red t-shirts in xlarge without have a column (red shirt - xl).

Something for quick look ups - shirts then more tables (I'm thinking) once a user drives down the search.

If all you want to know is how many of X you have, then why not just use a spreadsheet? Or is this supposed to be a web-based application?

[John_Blaze]

Yeah it's web application in which this is a small part. I'm just trying to think for a good DB design that is generic enough to be used in the future for many products.

For example:
Shirt red XL
1000 shirts
200 red
100 XL

I want to break it out for max flexibility.

[trudge]

You already have the basic information you need to start the database: item name, color, size, cost, price, etc. So make those fields in a Products table and start populating them. Include an ID field to tie in with future tables (suppliers, customers, invoices, etc.)

[John_Blaze]

yeah - its working, but thinking on simplifying a bit. As building a inventory system from these tables is becoming difficult.

[trudge]

What tables? Do a 'describe' for each table and post it, along with what exactly you are having trouble with.