I'm new to databases and am making an application type-thing for myself and my husband to use, as practice, and hopefully proof of my skills at some point.

 

I'm having a hard time getting my head around how to structure the database. This is a food tracking application. So far I have a table for user profiles, foods and recipes.

 

Each user profile has username, password, and preferences such as calorie allotment etc.

 

I need a way of storing information for each day. So database foods used, any calories added in separately, and daily totals.

 

Not sure how this would usually be done. Would you have a table for each day, prefixed with the user id/name and date, and store the data in that? It seems like you'd end up with a very large and confusing database, though, with that solution.

 

Suggestions? Though please don't give too much away if possible, I'd like to figure this out on my own as much as I can

 

Edit - A table for each user's records, with a row for each day, maybe?

Edited February 25, 201610 yr by rhubarblover

I'd probably have one table that holds the user records (UserId primary key) and another table that holds the tracker (UserId foreign key) with a new record added for each day.

 

You could also separate the preferences into a new table to avoid having potential null records in your user table (for users with no preferences, for example). So you'd do something similar to the tracker table and have a UserPrefs table with UserId as the foreign key.

Thanks for the reply. When you say a table that holds a record for each day, what exactly do you mean? The daily totals? If so, I was thinking about this and decided it probably actually isn't necessary to store them as the totals could be easily worked out whenever the user accesses data for a particular day - what do you think?

 

Also, is there a particular reason to have one preferences table rather than one for each user? Is it more efficient?

Thanks for the reply. When you say a table that holds a record for each day, what exactly do you mean? The daily totals? If so, I was thinking about this and decided it probably actually isn't necessary to store them as the totals could be easily worked out whenever the user accesses data for a particular day - what do you think?

 

Also, is there a particular reason to have one preferences table rather than one for each user? Is it more efficient?

 

What I mean is, you have one table that stores a record for each user, for each day. Something like this:

 

Id UserId Day

1 1 2016-02-26

2 1 2016-02-25

3 2 2016-02-25

 

As you can see, there are two records for UserId 1, one for the 26th Feb and another for the 25th Feb. All user records are stored in the one table. It's easy to filter this way, for example if we wanted to get all records for UserId 1, we do a simple SELECT * FOM tblTracker WHERE UserId = 1 and that returns all records for that user, we could even order it by the Day field to get records in date order.

 

It's better to have only one table to hold all preference data for many reasons:

 

• If you had multiple tables (one for each user), you'd need to match each user to its corresponding table
• If you needed to add/update a column, you'd have to update it in many tables
• What happens if you end up with millions of users? You wouldn't want to create a table for each one, with only one record.

 

I'd have a look into relational databases and look at some visual representations of common set-ups with users and relational data. It'll certainly help to see how things link together.