i'd like to make a recipe wiki with a twist or two. here are a couple things i'm wondering:
- since everything (users included) will be commentable and rateable, should i make a database-wide auto-incrementing counter for items independent of their own table's primary key? that way, recipes, ingredients and users would be assigned an 'article_id' in addition to their recipe_id, ingred_id or user_id, and the comments table will only need to contain "article_id, comment_content"). otherwise it looked like i'd need separate tables for recipe comments (reference recipe_id), ingredient comments (reference ingred_id), etc.
- if items are rated, is it a good idea to cache the current score? each time someone rates something, an entry of "article_id, user_id, rating" will be created, but i don't want to have to scan the entire ratings table each time a page is viewed to recreate something's average score. is it that bad to cache the current rating in a separate rating cache table?
- what's a good way to represent a recipe's ingredients and steps? my current favorite method:
Code:
table recipe:
-recipe_id
-name
-blurb
table ingredients:
-ingred_id
-name
-blurb
table quantities:
-quantity_id
-quantity_name
table recipe_ingredients:
-recipe_id
-ingred_id
-quantity_id
-quantity_amount
table recipe_steps:
-recipe_id
-step_number
-step_content
a recipe will have multiple entries under recipe_ingredients, such that a cake can contain "1 teaspoon flour, 2 cups sugar, 3 cups molasses" or some such. i'd also like to be able to convert measurements between imperial and metric, which is why i made quantity methods their own table, but what's a good way to actually perform the conversion? how about this:
Code:
table quantities:
-quantity_id
-imperial_name
-metric_name
-metric_multiplier
thus if someone wants metric, the name is switched and the quantity is multiplied by the metric_multiplier. sounds like that would have to be client-side though. any way i can specify imperial/metric at the query level?
i know this has been a rambling post, but the general idea is a commentable editable recipe site. i'd appreciate any advice or references.
dBforums
>
db design for commentable recipe wiki
01-08-06, 15:53
Linear Mode
