Single master table or parent/child - best practice?

[Charlie8585]

I'm fairly new to databases. I'm preparing to start a little (experimental) company packing frozen smoothie mix packs for people. Constructing an Access Database to keep track of recipes & nutritional info.

Here's problem I'm not sure on. I have 'master' table tblIngredients containing information about ingredients I'm using, mainly nutritional info, (fats, kJ, carbohydrates etc.). However I'd also like to put in cost (to me) of each ingredient (e.g. I can buy a 500g bag of frozen strawberries at Tesco for £2, 1 kg of peanuts off Amazon for £10 etc.)

My question is: is it best practice to put price field (PricePer100g) for each ingredient in tblIngredients or in a separate table tblIngredientPrices, linked to tblIngredients by a one-to-one relationship (e.g. IngredientID)?

If I just wanted PricePer100g it would be most straightforward to just put it on tblIngredients. However I'd also like to record specific pricing info of how I got to that price, for example checking price of frozen blueberries: DatePriceChecked (9/4/2022), Retailer (e.g. 'Tesco'), SpecificProduct (e.g. 'Tesco Special Frozen Blueberries, 350g') etc. and it will change from time to time. It doesn't seem right to put all that information on tblIngredients with all the nutritional info.

What is the correct way to structure this sort of thing?

 

[GPGeorge]

"...and it will change from time to time..."
That's your answer. The values will change, and if it matters both WHEN and HOW they change, you need a table for IngredientPrices to capture that history of changes.

 

[Charlie8585]

"...and it will change from time to time..."
That's your answer. The values will change, and if it matters both WHEN and HOW they change, you need a table for IngredientPrices to capture that history of changes.

Right - that answers that one! And I suppose that's simply a tblIngredientPrices with field IngredientID and other pricing fields (PricePer100g, retailer etc.) linked one-to-one to tblIngredients on IngredientID?

 

[The_Doc_Man]

A second reason for a separate prices table: Do you always get your ingredients from the same place? If not, do they sometimes have different prices?

 

[Mike Krailo]

Don't lock yourself into a field called PricePer100g, use a Units column to change the units as needed for different ingredients. You could also have another table that relates back to this one for expiration info so that when you enter a purchased ingredient, the expiration date automatically fills in for you.

Does the final recipe package get sold to normal consumers or are these products for other business or smoothie shops? I always thought you had to have a special blender to make a really good smoothie.

 

[GPGeorge]

It's clear from the two follow up questions that this is a lot more involved than it might appear at first. This is, in fact, a very good use case in the principle of designing a data model to suit the business model it is intended to track. One can't decide on data table designs until AFTER one has settled all of the relevant business rules that need to be incorporated. It can be an iterative process, though. You can start with your best effort and when something new pops up, don't be afraid to revisit that original design. E.g. Price Per Unit or different suppliers, and so on. Data models can't be set in stone up front, although we must make a good faith effort to get them into the best shape possible.

Thanks for the great reminder of the importance of being flexible.

 

[plog]

Tables shouldn't be in a one-to-one relationship (cue the pedants with their impossibly extreme edge cases). If data is related one-to-one, then it needs to go in the same table. First and last names are in a one-to-one relationship, you don't have one table for first names and one for last names you put both in the same table.

That said, you don't have a one-to-one relationship between ingredients and prices. It's one ingredient to multiple prices--the date field in the price table is how you differentiate them.

 

[Pat Hartman]

This situation is very similar to the common Order Entry problem. The items for sale change price over time and you need to be able to enter a price change ahead of its effective date. So, not only do you store the price with the order item (for convenience and also so it can be overridden), but you also keep a price history in a child table of the product.

 

[The_Doc_Man]

This particular pedant is merely going to amplify your comment, plog.

Charlie, plog's comment about one-to-one relationships can be explained this way. For Access and other relational databases, 1/1 relationships can be defined, but doing so requires both tables to use their prime keys for the relationship.

That PK requirement is because the "one" side of a relationship must be on a unique key if it is really going to specify one record. If this unique key ISN'T the prime key then at least it should have been a candidate for same. BUT if your unique key in this case isn't prime then normalization rules say you can't actually USE it as though a unique record depends on it. Only the "real" PK can be used to uniquely specify a record.

SO, the table that has a unique key must use that unique key as the PK, and in a 1/1 relationship, that means that the PK of both of these putative tables would be involved based on the same rules having a certain symmetry between the tables. So the PK on one side would match up against the PK on the other side. However, again from an understanding of normalization rules, if both tables' contents depend on the same prime key then the two tables ARE (or at least should be) describing different aspects (properties) of the same thing, which in turn says that the two tables should be one so that all of the properties of the described object/entity can be be together.

This is the usual reason that we strongly discourage 1/1 relationships.

 

[Charlie8585]

Thank you all for this. Excellent synopsis. Have read all replies in detail.

A second reason for a separate prices table: Do you always get your ingredients from the same place? If not, do they sometimes have different prices?

Same place - No. Different prices - Yes.

Don't lock yourself into a field called PricePer100g, use a Units column to change the units as needed for different ingredients. You could also have another table that relates back to this one for expiration info so that when you enter a purchased ingredient, the expiration date automatically fills in for you.

Does the final recipe package get sold to normal consumers or are these products for other business or smoothie shops? I always thought you had to have a special blender to make a really good smoothie.

Good suggestion re expiration - will implement. Final recipe goes to normal consumers! However it's a bit different because customer can go online & make their own recipes (or pick from ones on website) - a bit like a Dominoes Pizza! We simply then measure out your recipe ingredients for each smoothie into a pack and deliver packs to you each week. You just empty ingredient pack into blender and blitz. That way it saves you (customer) time buying smoothie ingredients and measuring them out into blender (which is very tedious - done it myself for years!). Don't need a stupendous blender - a regular Nutribullet does a splendid job!

Plog, Pat, Doc Man - noted! Clear to me now after reading those replies that, as ingredient prices will change from time to time and (hopefully) as business grows I can start bulk-buying so suppliers will change too, it makes much more sense to put prices on child table. Differentiated, as Plog said, by date (so then I can have historical record).

Superb all, it's now clear.

 

[GPGeorge]

This particular pedant is merely going to amplify your comment, plog.

Charlie, plog's comment about one-to-one relationships can be explained this way. For Access and other relational databases, 1/1 relationships can be defined, but doing so requires both tables to use their prime keys for the relationship.

That PK requirement is because the "one" side of a relationship must be on a unique key if it is really going to specify one record. If this unique key ISN'T the prime key then at least it should have been a candidate for same. BUT if your unique key in this case isn't prime then normalization rules say you can't actually USE it as though a unique record depends on it. Only the "real" PK can be used to uniquely specify a record.

SO, the table that has a unique key must use that unique key as the PK, and in a 1/1 relationship, that means that the PK of both of these putative tables would be involved based on the same rules having a certain symmetry between the tables. So the PK on one side would match up against the PK on the other side. However, again from an understanding of normalization rules, if both tables' contents depend on the same prime key then the two tables ARE (or at least should be) describing different aspects (properties) of the same thing, which in turn says that the two tables should be one so that all of the properties of the described object/entity can be be together.

This is the usual reason that we strongly discourage 1/1 relationships.

Here's a slightly contrarian view of the matter of 1-1 relationships. Sometimes (awful weasel word, but it fits here), a situation calls for discrimination among members of an entity grouped into a single table.

I'm going to use the generic "Company" table to illustrate the point.

If a data model has tables for, say, "Vendors", "Shippers" and "Customers", then it is often the case that we say, "No, we have Companies and that includes OUR company, our Vendors, our Shippers and our Customers", so they are consolidated into a single "Company" table.

The next realization is that we need to track different attributes for "Shippers" and "Customers", but we can't throw those fields into the company table because, well, they don't all need the same attributes. Sub tables are born. "Shippers" returns in a one-to-one relationship with "Companies", carrying the attributes specific only to shippers, and so on for "Customers" and "Vendors". Even our own company, perhaps.

That fits with The_Doc_Man's obsrvations in his next to last sentence.

I suspect there are better examples of this approach and I'm quite sure it's pretty rare. However, when the business rules call for it, it's an option.

 

[Pat Hartman]

I've used this "entity" design pattern a couple of times. One of the driving factors is it allows you to avoid duplicating certain types of ancillary tables such as addresses, contacts, correspondence, and notes to name a few. Instead of having these four tables for each type of company, you have one set of tables and the FK points to the generic "company" table rather than to the individual type tables.