How to make table with multiple "sizes" and attached quantity?

[shebalord]

Hi ppl,

I'm trying to make a database table for a sneaker inventory display/controller, it will be displayed on website using Dreamweaver to pull out the records. I can't figure out how to have a "size" entry in the table, let me explain:

Here is my current table structure:

[CatID] [ProdID] [ProdName] [ProdType] [ProdDesc] [Quantity] [Price]
---------------------------------------------------------------------
[11] [TR-01] [Nike SB] [Running] [5] [$45]
[11] [TR-02] [Nike SP] [Color] [Running] [2] [$25]

Here is the problem, I need a "Size" field in there, but a sneaker will have multiple sizes (i.e. 6-11) and each size will have different quantities. Is it possible to incorporate a "size" field in my current table? or should I re-do my table in another way? How?

Thanx in advance.

 

[The_Doc_Man]

Typically, you need at least two tables for things like this. In your case, maybe even THREE tables.

Your primary table would show the catalog number, item's maker, perhaps the product type. The important aspect of this is that you need to normalize the DB. This means identifying a candidate (primary) key.

Your catalog number can't be the primary key for the full table in your example 'cause you have two models with different model numbers. Where you have variations like that, you need a second table in a many-to-one relationship with the primary.

So in the second table, you have your catalog number as the foreign key and the model number as either a primary key or a MEMBER of the primary key (in conjunction with the catalog number).

Now, here's where life gets trickier. If you are tracking data separately for each SIZE, you need to have the catalog number and model number as foreign keys in a third table, along with the size data and whatever else you are tracking per-size.

Where does cost go? I don't know. Depends on whether price varies with size. If so, it goes in the lowest table. If not, it goes into the middle table. What's the rule? Put items in a table if and only if the item depends ONLY and ENTIRELY on the prime key of that table. (It's a normalization thing.)

Is that all? Not really. You are tracking quantity-on-hand in the table that holds sizes. TECHNICALLY, this is wrong, too. Not that you can't do this for report-/web-generating purposes. But inventory is normally tracked by having transactions showing (items in - items out).

Each incoming shipment is an (items in) case. Each sale is an (items out) case. At any time, inventory should equal (in - out). Since you can't sell what you don't have, inventory shouldn't go negative on you - unless you allow bookings in advance of shipments.

But I digress, having answered your original question.