Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need some advice on how to layout the tables for a MySQL database.

I will first describe what I am trying to do:

  1. I have a table for users. (This covers basic info for now)
  2. A user will select a certain workout plan (say plan 1-6). Different workout
  3. The user will then check a number of checkboxes. Say there are 15 checkboxes for the different machines, a user can select however many he/she wants. Let's say the user choose 6 different checkboxes, not necessarily in order. Each checkbox would have a certain ID. Those checkboxes could post a value of 1 to the database if checked
  4. On the next page a list of every checkbox the user clicked would be generated. Each checkbox has a picture and name associated with it. EX: For gym machines
  5. Each machine the user chose is displayed along with a certain number of sets that is set by an admin

Each plan has certain workout machines. I was thinking of making a users table and machines table. Each has user_id. So my database would keep track of each machine the user chose.

I already have the layout pretty much done. I just need to know how to layout my tables in the database so I can start populating data dynamically.

Do I just make 1 table for users and ad like 40 different columns? Probably not.

So let's say a user has clicked 6 machines, each of those machines gets a value of 1. Then on the next page each machine with a value of 1 is displayed along with the picture and name associated with that machine. The number of sets will be set by the admin.

And ideas? I can clarify further if needed.

If you want a better idea of what I am doing, take a look at this picture:

enter image description here This picture shows the page after the user has selected which machines he/she wants. You can see that 12 machines were chosen. So 12 different machines were chosen and they were all listed here.

The sets at the top were created by an admin and also reflect each machine shown at the bottom.

Everything is being populated from a database and I already have the layout done I just need a good recommendation for a table structure.

share|improve this question

migrated from May 16 '13 at 7:31

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 2 down vote accepted

Try this


|      1 |  Peter |
|      2 | George |


|         1 |     treadmill |      10 | blahblah |
|         2 | leg extension |       5 |  xyz.jpg |
|         3 |           xyz |       6 |      asd |


|      1 |     xyaz |
|      2 |     bbbb |


|      1 |         1 |
|      1 |         3 |
|      2 |         1 |
|      2 |         2 |
|      2 |         3 |


|      1 |      1 |
|      2 |      1 |
|      2 |      2 |


|      1 |         1 |    1 |
|      2 |         2 |    1 |
|      2 |         3 |    1 |

Only 1 concern is that how you will manage if user selects a new plan. so for this you have 2 approaches

  1. Delete the user existing plan from userPlan and userPlanDetail table

  2. If you want to maintain history of user selected plan you can then you can a have a flag field like which I have used in the above table which denotes that this plan or machine is under current use of the user or not.

If you are following 2nd scenario then while inserting records in the userPlan and userPlanDetail table use INSERT ON DUPLICATE KEY UPDATE.

Other tables are quite simple i don't think so it needs any explanation, post if you have any doubts. Hope this helps...


share|improve this answer
Comments are not for extended discussion; this conversation has been moved to chat. – Paul White Oct 20 at 1:00

Just create following tables:

  1. plans_master plan_id as PK and attributes as required.
  2. machine_master machine_id as pk and plan_id as foreign key references
  3. plan_master (plan_id)
  4. user_basic_info userid as PK and attributes as required.
  5. users_plans user_id, machine_id

To store the user-selected plan (mentioned in point 2), I think you have to confirm from front end, like "Are you sure want to take these plans?" - if yes then insert. You should take this confirmation on a second page where all machine snaps come.

share|improve this answer
Yes when the user is signing up he/she chooses the plan. Let's say he chooses plan 2. Plan 2 has 15 machines to choose from. Then the user selects the machines he want, let's say 8 machines. The next page shows the 8 machines the user chose with the sets(the picture) – TheNameHobbs May 16 '13 at 17:42

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.