How should I structure my database to stop having so many tables?

Question

Ok so i have a website in progress that will allow users to build routines using a set list of exercises provided. They can add a number of exercises to a routine which they will create and title themselves.

So i'm thinking i should have a user table. Each user will have a number of routines. Each routine will have a number of exercises.

Therefore I will have 3 tables: user, routines, and exercises

BUT

How can i link the 'routines' and 'exercises', because 'routines' is going to have to be more than one table, because if you imagine a user creates "biceps" routine and fills it with exercises. Then he creates "triceps" and fills it with exercises. He will then have to have two routine tables called 'biceps' and 'triceps'.

So how can I structure my database to accommodate this?

bearing in mind each user has a unique ID Every exercise has a unique ID and Every Routine has a unique ID.

Answer 1

You don't want to have two routines tables.
You want one routines table, plus an exercises table that references the routines table.

Depending on whether one routine is the same for all users, or whether each user can have his own version of the "biceps" routine, maybe you need one more table.
Take a look at the examples below!

---

Example 1 (each user can have his own "biceps" routine):

Users table:

UserId  Name
----------------
1       Joe
2       Bill

Routines table:

RoutineId   Name     UserId
-------------------------------
1           biceps   1
2           biceps   2

Exercises table:

ExerciseId    RoutineId  Name
--------------------------------------------------------
1             1          first exercise of routine 1
2             1          second exercise of routine 1
3             2          first exercise of routine 2
4             2          second exercise of routine 2

---

Example 2 (there is only one "biceps" routine, and it's the same for all users):

Users table:

UserId  Name
----------------
1       Joe
2       Bill

Routines table:

RoutineId   Name  
------------------
1           biceps

Users <--> Routines link table:

UserId   RoutineId
--------------------
1        1
2        1

Exercises table:

ExerciseId    RoutineId  Name
--------------------------------------------------------
1             1          first exercise of routine 1
2             1          second exercise of routine 1

---

EDIT:

You said:

And yes, routines are private to each user because they create them. They are not shared

and:

Each exercise should be able to be used more than once. I.e i might have a 'pull ups' exercise in my "back workout" routine, and my "arm workout" routine

Final Solution:

Users table:

UserId  Name
----------------
1       Joe
2       Bill

Routines table:

RoutineId   Name                    UserId
-----------------------------------------------------
1           Joe's back workout      1
2           Joe's arm workout       1
3           Bills's back workout    2

"global" Exercises table:

ExerciseId    Name
--------------------------------------------------------
1             pull up
2             whatever

Routines <--> Exercises link table:

RoutineId  ExerciseId
----------------------------------
1          2
2          1
3          1

That way, the "pull up" exercise is in Joe's arm workout (routine 2) and Bills's back workout (routine 3), but not in Joe's back workout (routine 1).
The "whatever" routine is in Joe's back workout (routine 1) only.

Is this what you wanted?

Answer 2

create table users(
userid int not null AUTO_INCREMENT PRIMARY KEY,
firstname char(30) not null,
lastname char(30) not null
);

create table routines(
rid int not null AUTO_INCREMENT PRIMARY KEY,
userid int not null,
rname char(30) not null
);

create table execises(
eid int not null AUTO_INCREMENT PRIMARY KEY,
rid int not null,
ename char(30) not null
);