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:
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:
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.
i like that step_id idea. what did you create that nifty diagram with? i've been looking for a rad tool capable of linking tables like that, every open source one i've seen has been woefully crash-prone and couldn't link tables anyway.
i'm also now wondering about my category system, but i started another thread for that since it's a more general question (i guess).