Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006
    Posts
    18

    db design for commentable recipe wiki

    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.

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    You seem to have a solid understanding of your data.

    I would treat comments/ratings on recipes, ingredients, user's etc separately and have them on different pages of the wiki. It's a simpler structure ultimately.

    Caching ratings in a table is okay.

    Your tables are fine. I would have a step_id as the primary key and the step_number as a unique natural key. This would allow for editing.

    For metric and imperial simply have your wiki have two queries and choose which to use accordingly.
    Attached Thumbnails Attached Thumbnails untitled.JPG  

  3. #3
    Join Date
    Jan 2006
    Posts
    18
    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).

  4. #4
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Google dbdesigner

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •