How to structure a variable product rating database

[JenniferMurphy]

For many years I have more or less kept track of how much I liked various products, usually with a rating on a 0-100 scale where 0-20 is terrible, 20-40 is poor, 40-60 is average, 60-80 is good, and 80-100 is excellent. This method sorta works, but it has some serious limitations. For some time I have been meaning to get up to speed with Access so I can do this in a database.

I am now ready to give this a try, but I have run into an immediate design question. I understand the basic database concepts of tables, records (rows), fields (columns), primary keys, foreign keys, relationships (1-1, 1-many, many-many), queries, forms, and normalization. What I need help with is how to design the tables and relationships for this application.

I would like a single database for all, or almost all, of the products that I use and want to rate. This would include food, household supplies, clothing, medications, etc. The problem I am having is that these products have very different properties. Here are some examples of products that I have in one of my Excel workbooks with the properties for each.

These are the tables that I think I will need to start:

1. Vendors. It will contain all of the places where I buy things: Amazon, Safeway, CVS, ...
2. Products. I am not sure if this is a table on its own or the result of one or more junction tables.
3. Purchased. One record for each purchase of each product.
4. Ratings. One record for each time I rate a product.

I am sure there will be other tables and some junction tables for the many-to-many relationships (e.g. Products-Vendors).

I don't know what to do about the brands, flavors, colors, styles, etc. I need a way to "connect" specific properties to specific products.

I see 2 forms I need to start:

1. New Product Form. It will allow me to add a product. It will probably need a sub-form to allow me to add types and properties.
2. Rating Form. It will allow me to rate an existing product.

I see 1 report I need at first:

1. Display a list of specific types of products that can be filtered by product type and sorted by rating, date last purchased, etc.

I would appreciate suggestions for how to set up the tables and relationships. I need way to tell Access which properties are applicable to each product.

Please let me know if this wasn't explained clearly.

Thanks

 

[mdmilner]

There's going to be more than one way to arrange things and it always depends on what you want to get from the table.

How far down the properties list will you go? If you buy raisins, would you list origin and color? If you bought Oats, would you itemize the list of subtypes? Flour? Sugar?
At what stage of the process do you describe all the properties? Do you want to have to pick from a list describing all the properties or freeform add them as you purchase? aka, pick from a list but add more if needed?

You have to think about your relationships.
I'd lean towards one of the star schema patterns. At the most basic level, you'd have the common facts sitting in the middle of multiple dimensions around the perimeter.
It's extremely common to have multiple fact tables in what's often termed "extended".

Your fact table would likely focus on the purchases. You'd have a date (even though you could also use a date dimension) with references outward to the Vendor, Specific Products and Ratings.
While you could use plain text descriptive terms for the values in the fact table, this design lends itself towards a some sort of numeric (or alphanumeric) index value where the plain text names are in the dimensions "around" the center.

I'd ask a question about things like ratings. Are you likely to rate each individual purchase of each specific item (or not) - however you decide changes how you can design the relationships. If the item purchased on mm/dd/yyyy has a rating that could be different from any other identical purchase, you might put the rating into the fact table itself. If each item only has one rating, you might put it into the product description table.

Oh, incidentally, the way you want to record the properties of an item is in it's own table.
You'd have an index, the name of the property and the value (as the 3 columns).

Mike

 

[JenniferMurphy]

Mike,

I have to apologize. I am working on two related projects. This one is for evaluating products already purchased. It is the simpler project. The other one is for evaluating products prior to purchase. That is one that focuses more on the individual properties and is the one that my misplaced comments about properties in this thread refer to. I got them mixed up. Sorry about the confusion.

Here's a mini-sheet for one of my product rating sheets. I think it gives a very good sense of what the database for this project should cover. This one is for soup.

The table on the left (TblProducts) has one record for each of the products that I have purchased. Most of the columns are calculated and not table fields. I would probably have another table (TblPurchases) to record each purchase showing the date, the price, and the store.

The table on the right (TblLog) has one record for each time I tried a product and recorded a rating.

I think this should be a fairly straightforward database application. Do you agree?

Product comparison, Food.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3		Product Table												Usage Log Table
4	Brand	Flavor	Dup?	Count	Avg Rtg	High Rtg	Low Rtg	First	Last		Date	OK?	Brand	Flavor	Rating	Comments
5	Progresso	Chicken & Homestyle Noodles	Ok	4	90.3	95.0	87.0	10/29/23	3/15/24		3/15/24	Ok	Progresso	Chicken & Homestyle Noodles	95
6	Progresso	Italian-Style Wedding	Ok	2	86.0	92.0	80.0	10/16/23	2/10/24		3/11/24	Ok	Progresso	Chicken & Homestyle Noodles	92
7	Progresso	Creamy Chicken & Homestyle Noodles	Ok	1	83.0	83.0	83.0	12/29/23	12/29/23		3/09/24	Ok	Progresso	Homestyle Chicken with Vegetables & Pearl Pasta	70	Odd aftertaste
8	Progresso	Chicken Noodle	Ok	1	82.0	82.0	82.0	6/28/23	6/28/23		3/04/24	Ok	Campbell's	Pub Style Chicken Pot Pie	60	Not great
9	Progresso	Hearty Chicken Pot Pie Style with Dumplings	Ok	2	79.0	80.0	78.0	12/11/23	12/18/23		2/22/24	Ok	Progresso	Italian Sausage & Potato	85	Good as a side to a potato
10	Progresso	Homestyle Chicken with Vegetables & Pearl Pasta	Ok	2	78.5	87.0	70.0	2/11/24	3/09/24		2/18/24	Ok	Progresso	Italian Sausage & Potato	40	To spicy
11	Campbell's	Chunky Chicken Noodle	Ok	1	76.0	76.0	76.0	5/25/23	5/25/23		2/11/24	Ok	Progresso	Homestyle Chicken with Vegetables & Pearl Pasta	87	Very good
12	Amazon Kitchen	Chicken Noodle	Ok	1	75.0	75.0	75.0	10/29/23	10/29/23		2/10/24	Ok	Progresso	Italian-Style Wedding	92	Very good & filling, could make 2 meals
13	Amy's	Thai Coconut	Ok	1	75.0	75.0	75.0	8/18/23	8/18/23		2/08/24	Ok	Progresso	Lasagna-Style Soup with Italian Sausage	75	A little too spicy
14	Campbell's	Chunky Chicken and Sausage Gumbo	Ok	1	75.0	75.0	75.0	3/04/23	3/04/23		2/03/24	Ok	Progresso	Chicken & Homestyle Noodles	87	Maybe better for 2 meals
15	Progresso	Lasagna-Style Soup with Italian Sausage	Ok	1	75.0	75.0	75.0	2/08/24	2/08/24		1/29/24	Ok	Campbell's	Chunky Creamy Chicken Noodle	60	Odd aftertaste
16	Amazon Kitchen	New England Clam Chowder	Ok	1	70.0	70.0	70.0	10/29/23	10/29/23		1/11/24	Ok	Progresso	Creamy Mushroom	70	Ok for half a can
17	Progresso	Creamy Mushroom	Ok	1	70.0	70.0	70.0	1/11/24	1/11/24		12/29/23	Ok	Progresso	Creamy Chicken & Homestyle Noodles	83	Fairly good
18	Progresso	Italian Sausage & Potato	Ok	2	62.5	85.0	40.0	2/18/24	2/22/24		12/18/23	Ok	Progresso	Hearty Chicken Pot Pie Style with Dumplings	78	Not great
19	Campbell's	Chunky Creamy Chicken Noodle	Ok	1	60.0	60.0	60.0	1/29/24	1/29/24		12/11/23	Ok	Progresso	Hearty Chicken Pot Pie Style with Dumplings	80	Good, but not great
20	Campbell's	Pub Style Chicken Pot Pie	Ok	1	60.0	60.0	60.0	3/04/24	3/04/24		10/29/23	Ok	Amazon Kitchen	Chicken Noodle	75	Ok
21	Campbell's	Chunky Beef with Vegetables	Ok	1	40.0	40.0	40.0	3/04/23	3/04/23		10/29/23	Ok	Amazon Kitchen	New England Clam Chowder	70	Tastes pretty good, but clams very chewy
22	Amy's	Black Bean Vegetable	Ok	1	25.0	25.0	25.0	9/26/23	9/26/23		10/29/23	Ok	Progresso	Chicken & Homestyle Noodles	87	Very good
23											10/16/23	Ok	Progresso	Italian-Style Wedding	80	Good, but not great
24											9/26/23	Ok	Amy's	Black Bean Vegetable	25	Yukky
25											8/18/23	Ok	Amy's	Thai Coconut	75	Ok
26											6/28/23	Ok	Progresso	Chicken Noodle	82	Pretty good
27											5/25/23	Ok	Campbell's	Chunky Chicken Noodle	76	Needs a little salt
28											3/04/23	Ok	Campbell's	Chunky Beef with Vegetables	40	The cook-in-package is junk
29											3/04/23	Ok	Campbell's	Chunky Chicken and Sausage Gumbo	75	A little too spicy
Soup

Cell Formulas
Range	Formula
D5:D22	D5	=IF(COUNTIFS([Brand],[@Brand],[Flavor],[@Flavor])=1,"Ok","Dup")
E5:E22	E5	=COUNTIFS(TblLog[Brand],[@Brand],TblLog[Flavor],[@Flavor])
F5:F22	F5	= IF([@Count]>0,AVERAGEIFS(TblLog[Rating],TblLog[Brand],[@Brand],TblLog[Flavor],[@Flavor]),"n/a")
G5:G22	G5	= IF([@Count]>0,MAXIFS(TblLog[Rating],TblLog[Brand],[@Brand],TblLog[Flavor],[@Flavor]),"n/a")
H5:H22	H5	= IF([@Count]>0,MINIFS(TblLog[Rating],TblLog[Brand],[@Brand],TblLog[Flavor],[@Flavor]),"n/a")
I5:I22	I5	= IF([@Count]>0,MINIFS(TblLog[Date],TblLog[Brand],[@Brand],TblLog[Flavor],[@Flavor]),"n/a")
J5:J22	J5	= IF([@Count]>0,MAXIFS(TblLog[Date],TblLog[Brand],[@Brand],TblLog[Flavor],[@Flavor]),"n/a")
M5:M29	M5	=IF(COUNTIFS(TblProducts[Brand],[@Brand],TblProducts[Flavor],[@Flavor])=1,"Ok","Error")

Regarding the other project, your comment about star schemas was very helpful. I'll read up on "star schemas" and "snowflake" schemas. Thanks

 

[mdmilner]

Quick question. Is it an option to use any BI type software? Conditional counting is a very common use item in products like Tableau and PowerBI.
So anytime you need to do calculations in a final result, in nearly all cases, you want to use a view/query to do that and not store it as a table value unless it's truly static and a final value.

If you were working in SQL, partitions strike me as the easiest way to do that kind of calculation or possibly CTE's. As Access doesn't support either,
What I'd suggest for you is you use the QBE wizard (query by example) and look at both of the standard offerings - "Find Duplicates" and "Find Unmatched" and see how they work.
The next thing is, subqueries. In Access, as far as I know, you can use CTE's in an external SQL statement (such as targeting SQL server) but you can't, inside MS Access. You can, however, make a distinct query and then join it to your base table. This is how you can replicate what partitions can do.

Partitions are a way of taking the base data (as written in the FROM segment) and then aggregating to produce a calculation for only a segment of the data.

SELECT A.COL1, B.COL2
,B.COL3
FROM A
JOIN B ON A.COL1 = B.COL1

Table B is going to be something like:

SELECT B.COL1
,B.COL2
,SUM(B.COL3) AS COL3
FROM B
{WHERE PARAMETER CAN BE HERE}
GROUP BY
B.COL1
,B.COL2
{AGGREGATION HAVING CAN BE HERE}

Summarizing, you will have your base table. Anything that's calculated that also looks at the entire table to get an average may need it's own GROUP BY type of query to do that possibly conditional aggregation. Functions like min/max/average are all available. If you need to get the first entry, make sure you add an ORDER BY clause because you can never rely upon tables to know which is first without explictly specifying it.

Hope that's not too general for you. Me personally, I would not recommend bothering with the built in Report options but using Access as your backend and Excel as your front end for smaller scope projects works really well.

 

[JenniferMurphy]

Quick question. Is it an option to use any BI type software? Conditional counting is a very common use item in products like Tableau and PowerBI.
So anytime you need to do calculations in a final result, in nearly all cases, you want to use a view/query to do that and not store it as a table value unless it's truly static and a final value.

Wow. This sounds like exactly what I need. Let me do some studying. Thank you!

 

[mdmilner]

You're welcome. You'll still want to store the data somewhere convenient to you but functions like COUNTIF work almost exactly like how they'd work in Excel (in Tableau/PowerBi).
You'll end up using their scripting language to do it though - DAX in PowerBI.

One thing I'd like to mention is normalization of data.
Making one row of data with a whole bunch of columns is basically "first normal form".
For smaller projects, going into the weeds and building it like you would a datawarehouse is overkill.