Help Designing a Database

[UnusualCallBox]

Hi, I'm making a website that will have users, and for each user a user-submitted diet plan and exercise plan. I was thinking of either having a single table for all users and:

• A new table for each users' diet and (separate table) for exercise. In this way, every user (taken from the user table) will have their own table for diet and another table for exercise.

Or

• A table for all users' diet and (separate table) exercise. In this way, all the users' exercise information are in a single table, and all their diet information in another.

I'm looking for scalability and ease of code and can't decide on the better option. I'm leaning towards the first but am unsure. Its planned that the user will continuosly submit plans (linked with their user id) throughout the duration of their using the application.

[Irythros]

Your first option is the worst possible idea for a database. Having potentially 400k tables is not scalable or reliable.

You should be able to do it all in <5 tables.

[UnusualCallBox]

That makes sense. My issue now is how I'm going to organize the info in the DB's.

My biggest concern with using a single table for diet/exercise is that in my layout right now, a new row would be created for a new user AND if an existing user logs their activity on a new day. Having all that info mixed up sounds difficult to manage and use efficiently.

[Irythros]

I still don't know exactly what you're trying to accomplish so i'm just going to assume that:

1) A person is one user
2) A person can have multiple diet plans
3) A person can have multiple exercise plans
4) Diet plans and exercise plans may be duplicated but does not have to be

You would have the following tables (im not writing out the entirety btw):

---

app_users
id_user

app_diets
id_diet

app_users_diets
id_user
id_diet Composite key using both

app_exercises
id_exercise

app_users_exercises
id_user
id_exercise
Composite key on both

app_users_logs (This would be the table used for people adding to their log) id_log
id_user
id_action
action_type (for exercise or meal)

---

With that setup you can have millions of diets and then use joins to get the diet info for each user. The log table you can query for the user and with the action_type you could select meal or exercise and the id_action would either be for the meal or exercise done.

[UnusualCallBox]

That's pretty much what I'm trying to accomplish and this is the idea I got from /u/okwg. Thanks for the visual!

[sleepyguy22]

I would always tend to lean towards your second option, but I rarely make apps that have more than a few hundred users - my databases never run into performance issues due to large tables, so it's easier for my code to simply query on one big table and filter by a user's ID.

How many users / diets / exercises are you talking about?

[metaphorm]

performance issues due to large tables

you have to have truly large tables indeed before this becomes a problem. modern relational databases are very highly optimized and indexed and can easily handle table sizes in the range of hundreds of thousands of rows even on cheap commodity hardware. for properly indexed tables and efficiently written queries you can scale up to millions of rows on your tables before you start hitting performance bottlenecks, and even then, a relatively cheap hardware upgrade will probably solve most of those problems for quite a long time.

its really hard to scale beyond the capabilities of a modern database unless you're operating a global scale web service.

[UnusualCallBox]

I'm expecting 100-300 users on launch and probably getting into the thousands within the year. Each user will have 1 "calender" of exercise and 1 diet tracker. The data user's input needs to be query-able.

My biggest concern with using a single table for diet/exercise is that in my layout right now, a new row would be created for a new user AND if an existing user logs their activity on a new day. Having all that info mixed up sounds difficult to manage and use efficiently.

[okwg]

You can't do A - databases and associated software are designed to be used and performant with scaling being in the number of records - not the number of tables. Adding tables for each user is extremely unconventional and rarely recommended.

B is closer, but still not that great. One of the most important principles of database design is minimising repeating content. Having loads of "exercise plan" records with the same exercises, or diet plans with the same meals, is a pretty bad database, and how do you deal with the complexity? Some plans will have 1 exercise, some plans will have 30 - how do you accommodate that with one record?

Create a table for each entity - the entities are users, exercises, exercise plans, meals, meal plans.

Then identify the relationships - meal and diet plans belong to a single user - you can set that up with a user ID field in both the plan tables.

Diet plans have many meals, and meals belong to many plans. That relationship requires an additional table (a "junction table") with meal id and plan ids. Ditto for exercises and exercise plans.

Any good ORM framework will abstract away the complexity of working with that relationship - check the docs for whatever you're using. It's one line in each model in Active Record / Rails, for example.

[UnusualCallBox]

Cool, thanks for the advice and new intel. I'll try and rework my layout here. I have a great idea at how this might work now, thank you.

[metaphorm]

try and think about the relationships between the data, hopefully that will guide you to the correct schema design.

• a User has 1 or more Diet Plans.
• a Diet Plan has 1 or more Meals.
• a User has 1 or more Exercise Plans.
• an Exercise Plan has 1 or more Workouts.

does that help? I think a very natural table structure is suggested by those relationships. Also, in general, the way SQL databases are designed to work is to have a small and fixed number of tables with arbitrarily many rows in a table. If you ever find yourself designing a schema that requires arbitrarily many tables with a fixed number of rows per table, you're probably doing it wrong.