CODEWITHSUNDEEP
sqldatabase-design
chobo2
chobo2

Reputation: 85765

Should I Merge This Database data into one table?

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.

Upvotes: 0

Views: 168

Answers (3)

parkerw262
parkerw262

Reputation: 334

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

Upvotes: 0

Smandoli
Smandoli

Reputation: 7019

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.

Upvotes: 0

KeithS
KeithS

Reputation: 71563

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).

Upvotes: 2

Related Questions