Mysql – Recommendation for theSQL table structure (no code)

database-designMySQL

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.

Best Answer

Try this

userMaster

| USERID |   NAME |
-------------------
|      1 |  Peter |
|      2 | George |

machineMaster

| MACHINEID |   MACHINENAME | NOOFSET | IMAGEURL |
--------------------------------------------------
|         1 |     treadmill |      10 | blahblah |
|         2 | leg extension |       5 |  xyz.jpg |
|         3 |           xyz |       6 |      asd |

planMaster

| PLANID | PLANNAME |
---------------------
|      1 |     xyaz |
|      2 |     bbbb |

planDetail

| PLANID | MACHINEID |
----------------------
|      1 |         1 |
|      1 |         3 |
|      2 |         1 |
|      2 |         2 |
|      2 |         3 |

userPlan

| USERID | PLANID |
-------------------
|      1 |      1 |
|      2 |      1 |
|      2 |      2 |

userPlanDetail

| USERID | MACHINEID | FLAG |
-----------------------------
|      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...

SQL FIDDLE

Related Question