Getting back on topic the problem your having is more understandable given your table design. Your design needs to include a table that accounts for a physical shirt before the design is applied. That way you can actually manage the inventory of your physical items such as; red XL t-shirt.
Shirt component
----------------------------------------------------------------------------
Style | Color | Size | Inventory
----------------------------------------------------------------------------
TSM White XL 2
SSF Forest S 1
TSF Black MD 10
This table bridges the divide between what are the physical items in your store. The physical items being the shirt or application surface before the design is applied. That way you can monitor each application surface individually and track the real-time inventory of each appropriately. The design itself is a separate entity thus its inventory can be placed directly inside the design table. Although, it doesn’t seem like a design would ever be out of stock since your applying designs to a surface. If you have the surface you can apply the design – correct?
sorry, oddz, i see the generic/specific discussion as important, and not all that complex
t-shirts come in styles, sizes, and colours – so the question is, how do you implement the one-to-many relationship between the generic t-shirt and the various specific available t-shirts
most of the time, i recommend a single table (which might involve repeated values in some columns), so that’s the simplest solution
“brand” tables are often implemented in inventory applications, with a one-to-many relationship to the products table, and the question is what are the reasons for needing a separate table, so that’s why i turned the question back to tom
Honestly, you can very quickly loose yourself in all these different pricing rules. Unless you must cover a rule don’t. There is no system out there that covers everything. In your case you should focus on the rules that you plan on needing and not worry about all these complex scenarios at this time. You will likely go crazy trying to support a bunch of abstract pricing rules.
Its not really all that simple though. What you end up needing is a way to alter the price based on a rule. For example, if someone orders 12 the price per unit is 2.00. Even more complex is if person orders 12 the total is 10.00. You could even have combination of items that use rules. Like if person orders 2 x and 3y the price for x is 1.00 per unit and 3 y’s are a fixed price 40.00. All these different pricing rules can really place a tax the queries also. The whole thing can become very ambiguous extremely quickly and out of control. I’m not saying it can’t be done but you really do need defined terms in order to implement the functionality in an optimized manor
based on the columns you said might be found in the inventory table (How many do we keep in stock? At what level do we re-order? How many items are on hand?), these columns should all be incorporated into the products table
to determine whether you ever need a separate table, the first question is “how do the primary keys of these tables compare?”
if each product has only one row in the inventory table, then this matches the situation in the products table, and they can be merged
On a side note, and more so for later learning, how would you handle something more complex like…
What was the cost of a grouping of Products? (Maybe you bought a shipment of 1,000 Blue Widgets for $0.07/item last month, but last week you bought a shipment of 2,500 Blue Widgets for $0.12/item. So you need a way to manage your costs and keep track of the different costs of the same Product.)
What would be the most logical way to model this were the “Product (generic)” and the “Product Instances (specific)” are not the same?
depends on your requirements. if you need to find out if a product can be ordered only as backorder, you need the inventory table (or you can flag it as to be ordered as backorder only).
you don’t need the AmountOnBackOrder column, that can be calculated from the orders table.
ItemUnitCost also not needed, that should go to products table.
for the inventory table you can only use these two columns
ProductId
ItemsOnHand
That’s my point - I’m not sure what my requirements should be on this point?!
When I designed my Product table I had…
ID
SKU
Name
Description
Weight
UnitPrice
Off the top of my head, below are some things that would seem to apply more to “Inventory” than the Product" itself…
For a given Product…
How many do we keep in stock?
At what level do we re-order?
How many items are on hand? (I guess a negative # means “On Back Order”)
What was the cost of a grouping of Products? (Maybe you bought a shipment of 1,000 Blue Widgets for $0.07/item last month, but last week you bought a shipment of 2,500 Blue Widgets for $0.12/item. So you need a way to manage your costs and keep track of the different costs of the same Product.)
There are probably a lot of other things you need to manage Inventory?!
With this being said, what would be the relationship between an “Inventory” table and your “Products” table??
Since we are trying to start a T-shirt business, and we are new, my questions probably don’t apply at this point. But I figure it is better to ask now, versus realizing I screwed up my database design by not accounting for something like inventory?!
There certainly must be some common approachs to handling this topic. :scratch:
Well I was asking the question in the context of managing inventory costs. (Like I said, if you buy the same Product or raw material or whatever over time at different prices, you can’t just have one field for one product.)
Tangent…
However, what you are alluding to above is actually another topic unto itself.
We actually discussed that topic at length over at dbForums I believe. (Am I that forgettable?!)
Having spent the last couple weeks trying to cram on “OO Design Patterns”, I actually have to scratch my head and get back into that topic as well and figure out what I finally decided to do.
I believe what I decided was this…
There will be 4 COMPONENT tables:
Design component:
--------------------
1000 Rawwr
1001 Nom Nom
1002 Clever Girl
1003 B is for Bacon
Color component
--------------------
01 White
02 Black
03 Blue
04 Forest
Size component
--------------------
SM Small
MD Medium
LG Large
XL X-Large
X2 XX-Large
X3 XXX-Large
and there will be a PRODUCT table which represents a “Finished Good”.
It will have a SKU ( e.g. “1003-TSM-01-XL”) which is built from the 4 component tables.
(**NOTE: I believe you ultimately supported this decision.)
The PRODUCT table will contain every combination of T-shirt that is offered. (So if “B is for Bacon” does not come in a Men’s, Black, XL T-shirt then there would not be “1003-TSM-02-XL”.)
Back to this thread…
This, however, is a separate issue from what I asked above. I was just thinking in terms of Inventory and the kinds of things that might relate to an INVENTORY table (i.e. many instances of one Product) versus the PRODUCTS table (i.e. a “template” for each Finished Good T-Shirt).
