Normalisation Quandry

Results 1 to 2 of 2

Thread: Normalisation Quandry

  1. #1
    Join Date
    Dec 1969

    Default Normalisation Quandry

    I&#039;m designing a database for an online store but I&#039;m having a few small logic problems here.<BR>Basically there are three product types - t-shirts, CDs and other stuff. Each category contains goods with some common attributes and some unique attributes.<BR>I had an idea like this (it&#039;s a litle simplified)<BR>ProductMasterTable<BR>ProductID &#124 ProductDescription &#124 Price<BR><BR>tshirts<BR>TshirtID &#124 ProductID &#124 t-shirt_size<BR><BR>CDs<BR>CDID &#124 ProductID &#124 PlayingTime &#124 Producer<BR><BR>So far not too difficult, but then we come to inventory and the way I want to display each item. Say for t-shirt inventory, I want to enter seperate quantity amounts for different sizes. I only have one product (say a red t-shirt) but an inventory listing for each size.<BR>I want to have all the inventory in one table so I imagine it would be something like this.<BR>Inventory<BR>InventoryID &#124 ProductID &#124 InventoryPlus &#124 InventoryMinus<BR><BR>So for one product (the red t-shirt) I would have to have separate entries in the products table for each different size? Since the description etc. is only being displayed once, and the available sizes in a select box, I would have duplication of the description and price a number of times depending on how many sizes.<BR>Am I making sense?<BR>Should I have a product table with only an ID field in it? Would this be a little weird?<BR>Please save me from the confines of my thinking - I want to do this **** thing properly! Experience with non-normalised databases has taught me to try and get this right from the start or suffer eternal damnation!<BR>Any help greatly appreciated.<BR>Thanks<BR><BR>Greg<BR><BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default Why not just use a sub-ID...

    Inventory<BR>InventoryID &#124 ProductID &#124 SubProductID &#124 InventoryPlus &#124 InventoryMinus<BR><BR>For a TShirt, the SubProductID would be the TShirtID from the tshirts table.<BR><BR>For a CD, it would be the CDID.<BR><BR>Etc.<BR><BR>???<BR><BR>Now, if somebody wanted to know how many tshirts were on hand, regardless of color, you&#039;d do<BR> SELECT SUM(...) FROM Inventory WHERE ProductID=&#060;generic tshirt id&#062;<BR><BR>This is more in keeping with the way the "big guys" do it.<BR><BR>After all, you go to a grocery store, an 11 ounce box of Kellogg&#039;s Corn Flakes has a different UPC code than does a 15 ounce box than does a 22 ounce box and certainly different than an 11 ounce box of frosted flakes, etc.<BR><BR>And yet the store can easily find total sales of all Kellogg&#039;s cereals of any kind, by grouping on the supplier ID part of the UPC.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts