Database design strategy for a wine review site with top 10 lists and featured wines

Question

I'm building a Wine review site and have run into a database design problem I was hoping someone could help me with.

The basic premise of the site is that users will log in a leave reviews of wines they have tested.

• users
• wines
• reviews

A user has many reviews, review belongs to user and review belongs to wine. This much is easy.

I have two extra bits of information I want to show.

Each week I will have a list of the top 10 wines bought in supermarkets. This list is not based on data from the site but rather data given to me by the supermarket so I cannot calculate this myself and display.

I will also have featured wines which will be displayed on the front page. Again supplied by the supermarket.

My question is what kind of database design would be best in this situation, in terms of avoiding duplication and complexity.

For the top 10 list my initial thought is to add a decimal column to the wines table which will list its position in the top 10 list but this seems overkill for thousands of wines when only 10 are listed at once.

I'm thinking the same for featured wines, adding a boolean value to indicate if the wine is a featured wine or not. Again, it seems overkill and I don't want to maintenance problem of making sure only a certain number of wines are featured (as this will change week to week)

Thanks.

Answer 1

Why not have a featured_wines table and a top10_wines table that has wine_id in it? So you would only have rows in those tables that apply for the given condition (i.e. top10_wines would have 10 rows in it and featured_wines would only have the featured wine IDs in it).

Answer 2

If each wine has a rating than you can use that column to sort the wine into a top 10 list. You can also have a second column for rating of wine done my the store vs. the user. If you get a list of wine they recommend than you can use that list to rate that wine high.

Answer 3

Your best option is to add another table for featured wines and another table for top 10 wines. This way, you're not changing your wine table structure to support these features. Also you only have to query these tables to get the result instead of the entire wines table to find the top 10 or the featured wines.

Answer 4

I would make two new separate tables; top_wine_list, with the columns; 1st,2nd 3rd etc. Maybe some more columns for date_entered and so on.

This might not be best practice, but it sure is easy to work with, every row is a new "list of top wines". Or just add another column, "rating" for your "real wine" table, and rate all your wines!

featured_wine_list could be just id's to your other wines. Easiest one!

Answer 5

Create a single new table to store both types of features. It should include the following columns:

feature-type: The type of feature (TOP10, FEATURED, etc.)
wine-id:      The ID of the wine being featured
date-begin:   The date that this list begins
date-end:     The date that this list ends
rank:         The ranking of this wine for this list (order displayed for featured wines, or sales volume)

This structure allows you to retain historical rankings, add in new types of lists, and pre-add data for future weeks.

Answer 6

The common theme among the answers so far is to add tables for premium and top X wines (or have one table for these lists), which is great except for one problem. You mentioned you have no control over these lists and they are supplied externally by supermarkets. So it's possible they will send you a wine that you do not already have in your list, or a wine that is described differently but for all intents and purposes may be the same wine as one in your original list. This could cause complications if the wine name is not represented in these tables and instead you just have a link to the wineID from the wine table. So a couple of suggestions to mitigate this problem (if it's a real one for you):

1) Include a Wine Description field so that your supermarket overlords can describe the wine differently than you do in your reviews. 2) Consider linking via a standard ID that all parties are likely to agree on such as an SKU. This may avoid manual work on your part matching supermarket wines to the lists when they get imported.

Answer 7

Most have said the same I would say, with the exception I would add a Week table, to keep track of what to show when and allow you to pre-populate the lists. So you would add 3 tables

Table: Week
Fields: WeekID, StartDate, EndDate

Table: Top10Wines
Fields: WeekID, WineID, Position

Table: FeaturedWines
Fields: WeekID, WineID, OtherFields