SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question How would you structure a database for this type of website?

    Hi all,

    At the minute I'm throwing around ideas for a fitness-related website that allows users to log their meals (and thus calories etc.).

    I'm not particularly experienced in the design side of databases, as everything I've done up until now has always been a lot more simple than this will be. Technically I have the ability to code it and set it all up, it's the database design side that I'll need some pointers for. (I'm also aware that some such sites already exist.)

    Basically, the user experience will be as follows:

    User creates a login and password.
    -> User logs in and clicks "meal planner".
    --> User can then add foods to their day's meals (from a database of nutritional values in food) OR they can add custom foods that aren't in the database (since the database will mostly consist of very generic foods like apples, tuna etc.).
    ---> User can also click on days in the future (or the past, if they want to put in meal plans from the past that they may have been writing down up until now) and enter data. The database will store ALL of their data permanently unless they choose to delete it.

    I don't want custom-entry foods to be in the "main" database (i.e. for others to see), since it will open the system up for abuse, so custom-entered foods will be limited to that user.

    I also want the user to be able to keep a textual log if they want to, workout logs, logs of their weight/body fat percentage etc.

    At the minute the best idea I'm coming up with is creating a set of tables specifically for each user that creates a login, i.e.

    User creates login "johnsmith88".
    -> Tables 'johnsmith88_foodlogs', 'johnsmith88_log_entries' etc. are created.

    To me this seems like horrible database design, but I don't know any better. Can anybody make any recommmendations?

  2. #2
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    don't split the tables on users, split them on entitities. A sketch:

    body_info
    ============
    PK:user_id
    PK:date
    weight

    user
    =========
    PK:user_id


    ingredient
    ==========
    PK: ingredient_id
    name
    calories
    fat

    meal_user
    =======
    PK: meal_id
    PK: user_id
    PK: date

    meal
    ======
    PK: meal_id
    FK: ingredient_id
    quantity
    Last edited by plaatspunt; May 1, 2008 at 09:23. Reason: meal_user should have a date

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the tips plaatspunt, much appreciated.


Bookmarks

Posting Permissions

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