Products and Brands: A Database Question
I’m trying to consider the best way to organize a data store. Maybe some of my IT friends can shed some light on it.
I want a collection of standard household food products. The problem is how to handle different brands or sizes of the same product. Consider rice for example. We usually purchase family size bags of Success Boil-in-Bag white rice. When the grocery store doesn’t have the family size bags, we’ll purchase the normal bags. Occasionally, they don’t have any Success brand rice, so we’ll buy store brand or some other brand.
With just white rice, that would be at least four unique items that all serve effectively the same purpose. If I’m going to the grocery store to get rice, I don’t truthfully care what brand or size at the end of the day. Once the rice makes it to the place, I couldn’t care less what the box looks like. I need some way to consolidate each of those records into the same group.
UPC as Primary Key
At first, I considered making the UPC the primary key and having a field called “substitute” to indicate connections between items. An item with a null substitute field is the original record and the one that I would base any logic off of. Any related items would have the UPC of the original record in their substitute field.
Select * from Product where UPC=searchString or substitute=searchString
This query would return any products of the type that I want. It looks pretty clean, and I think it’s easy to understand. But what do I do about measuring other things like quantity? Again, I don’t really care what brand or size I have for cooking reasons. I just want to know how much rice I have. Do I only monitor and update the original product? How and when do I convert Laura Lynn white rice to Success? The actual maintenance gets really messy at that point.
Two Table Approach
Eventually, I thought that I could possibly create generic records for each of the products that I need in one table, and have specific brands and sizes in another table. One table Product might contain records named “white rice” and fields like “quantity” or other such measures. The primary key wouldn’t be as readable as something like UPC, but any kind of GUID should take care of that requirement.
The second table Brand would have each of those individual brands, with a field matching the primary key of the corresponding Product. With this relationship, Brand might have individual totals that Product takes the sum of, or Brand could have a specific count or quantity that updates the Product each time it is added. The distinction there would certainly need to be determined, but I think the differences between those options are less consequential.
Analysis
After considering both options, I think the two table approach is the better solution. I would probably interact directly with the Product table more, and stripping out all the individual brands would make that a little less cluttered. Plus, the individual Product records would have fewer fields to deal with. Brand might have units per item, where Product has total units of that product. I have a 4 bag box of Success rice, and an opened box of store brand rice with 2 bags remaining. All I care about for the Product table is that I have 6 bags of rice in total.
I want a collection of standard household food products. The problem is how to handle different brands or sizes of the same product. Consider rice for example. We usually purchase family size bags of Success Boil-in-Bag white rice. When the grocery store doesn’t have the family size bags, we’ll purchase the normal bags. Occasionally, they don’t have any Success brand rice, so we’ll buy store brand or some other brand.
With just white rice, that would be at least four unique items that all serve effectively the same purpose. If I’m going to the grocery store to get rice, I don’t truthfully care what brand or size at the end of the day. Once the rice makes it to the place, I couldn’t care less what the box looks like. I need some way to consolidate each of those records into the same group.
UPC as Primary Key
At first, I considered making the UPC the primary key and having a field called “substitute” to indicate connections between items. An item with a null substitute field is the original record and the one that I would base any logic off of. Any related items would have the UPC of the original record in their substitute field.
Select * from Product where UPC=searchString or substitute=searchString
This query would return any products of the type that I want. It looks pretty clean, and I think it’s easy to understand. But what do I do about measuring other things like quantity? Again, I don’t really care what brand or size I have for cooking reasons. I just want to know how much rice I have. Do I only monitor and update the original product? How and when do I convert Laura Lynn white rice to Success? The actual maintenance gets really messy at that point.
Two Table Approach
Eventually, I thought that I could possibly create generic records for each of the products that I need in one table, and have specific brands and sizes in another table. One table Product might contain records named “white rice” and fields like “quantity” or other such measures. The primary key wouldn’t be as readable as something like UPC, but any kind of GUID should take care of that requirement.
The second table Brand would have each of those individual brands, with a field matching the primary key of the corresponding Product. With this relationship, Brand might have individual totals that Product takes the sum of, or Brand could have a specific count or quantity that updates the Product each time it is added. The distinction there would certainly need to be determined, but I think the differences between those options are less consequential.
Analysis
After considering both options, I think the two table approach is the better solution. I would probably interact directly with the Product table more, and stripping out all the individual brands would make that a little less cluttered. Plus, the individual Product records would have fewer fields to deal with. Brand might have units per item, where Product has total units of that product. I have a 4 bag box of Success rice, and an opened box of store brand rice with 2 bags remaining. All I care about for the Product table is that I have 6 bags of rice in total.
Comments
Post a Comment