0

I want to store some product data in my database. At first I thought having a product table and product info table but not sure if I should just merge it all into one table.

Example

Coke - 355 ml

Product.Name = Coke
ProductInfo.Size = 355
ProductInfo.UnitType = ml

Coke - 1 Liter
Product.Name = Coke (would not be duplicated...just for illustration purposes)
ProductInfo.Size = 1
ProductInfo.UnitType = L

if I did this of then of course I would not be duplicating the "Name" twice. My plan then was I could find all sizes of the same product very easily as all I would have to do is look at the many side of the relationship for any given item.

Here is the problem though, all the data will be user driven and entered. Someone might write "Coke a Cola" instead of "Coke" and now that would be treated as 2 different products as when I go to look if a product has been entered called "Coke a Cola" but it won't know to check for "Coke" as well.

This leads me to having to do like partial matches to maybe try to find it but what happens if someone has some generic brand what would be "Cola" and that would get matched as well.

This gets me to think maybe there is no point to keep the data separate as to me it seems like a good chance everything will end up to be it's own product anyways.

chobo2
  • 83,322
  • 195
  • 530
  • 832

3 Answers3

2

There's merit in both approaches. Keeping them separate, the table you're calling "Product", I'd call "Brand" instead, and "ProductInfo" is your actual "Product" table, containing the information about the actual sellable item of that brand (a 12oz can or liter bottle of Coke).

Alternately, you could further normalize it into Brand, Product (here being Coke Classic as maybe opposed to Diet Coke or Caffeine Free Coke) and UnitSize (can or bottle; these would apply not only to Coke Classic, but Diet Coke, Pepsi or Dr Pepper).

If you denormalize this this data, you aren't duplicating much on the naming side of things, but you are duplicating quite a bit of unit of measure data. The question is whether it's more useful to ensure consistent branding of your product records (denormalizing means you'll need some other means to ensure your products have the same brand), or to avoid the joins between the two tables (there is a cost to joining, though it's typically small if you can join between indexed fields).

KeithS
  • 70,210
  • 21
  • 112
  • 164
  • I do have a brand table already so basically it is Brand has many Products which can have many ProductInfo. Info contains the size, barcode and stuff. The whole hope of ProductInfo would be that I could easily find all "Coke Classic" and all it's sizes. – chobo2 Jul 09 '13 at 17:29
0

The only compelling reason to make a Header-Detail arrangement, with two tables, would be if Coke has attributes that are the same no matter the packaging. Right now, I don't see any attributes like that; so one table covers it. You might say, "But I might think of something in the future like that." That may be a reason to make two tables; but (unlike many kinds of change to a database schema) this may not be too difficult to break into two tables later, when you know there is a need.

I see the point about mistakes that result in nearly-matching records. I think that's not a consideration at this table level and you should address it as a part of record editing.

Smandoli
  • 6,919
  • 3
  • 49
  • 83
  • The hope was with having it as 2 tables would be that I could just take any product and find all other sizes of that product by just looking at the many table of the product. Then I could recommend other sizes to the user but like I said I don't know how well that would work. – chobo2 Jul 09 '13 at 17:34
0

The best way to do this would be to have your product or item table in its own table with fields like ID, SKU number, short description, active, and so on… Then you have your “many” table hold the other item attributes which can be joined on ID; a one to many relationship. And to solve the user input issue, you have a combo box which is tied to inventory choices or item choices. This way you enforce data integrity. Well, that is how I have done it.

This post has some helpful links on DB design

Community
  • 1
  • 1
parkerw262
  • 336
  • 2
  • 4
  • I don't store the SKU number(that woudl be just another number the user would have to add in and probably turn more people off). I store the barcode and the problem with that is each size of "Coke" would have a different barcode so there is nothing to use as a solid join piece exepct the name. – chobo2 Jul 09 '13 at 17:33