Tell me more ×
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.
up vote 2 down vote favorite

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

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
1  
How workout plans and machines are related ? Each workout plan has some machines assigned? – Meherzad May 16 at 3:26
What data workout plan will have? – Zlatan May 16 at 6:11
Try use 3NF en.wikipedia.org/wiki/Third_normal_form – Justin May 16 at 6:45
Yes 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. – Bradley Hobbs May 16 at 17:40

migrated from stackoverflow.com May 16 at 7:31

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

2 Answers

up vote 0 down vote accepted

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

share|improve this answer
Where i get confused here is when you have user_id 2 having 2 plans. The user selects one plan at signup and if they want to change plans they can contact admin. So user_id 2 selects plan 2. Plan 2 has machines 1,2,3? but then in userPlanDetail the user_id 2 only has has machine 2,3? – Bradley Hobbs May 17 at 21:25
For the future i would probably do option 1. No need to keep track of user plans. – Bradley Hobbs May 17 at 21:36
I have selected only 2 machines because you have only mentioned in one of your points that user can select any number of machines within a plan(POINT 3). So user has selected machine 2 and 3 though he/she had option of 1,2 and 3. If you don't want such feature you can remove it.. Hope it helps.. – Meherzad May 18 at 4:51
Oh ok. And what about USerPlan where user 2 has selected plans 1 and 2? – Bradley Hobbs May 18 at 18:22
Yes you have mentioned that user can select multiple plan so user 2 has selected plan 1 and 2. If user updates his plan than first delete all the plan of that user and then add new entries of the new plan as you have mentioned that you don't want to maintain history of user selection.. – Meherzad May 18 at 18:41
show 21 more comments
up vote 0 down vote

Hi Just create following table

plans_master plan_id as PK and attrubutes as required. machine_master machine_id as pk and plan_id as foreign key references plan_master (plan_id)
user_basic_info userid as PK and attributes as required. and now users_plans user_id,machine_id

share|improve this answer
Where to store user selected plan as OP has mentioned in point 2 ? – Meherzad May 16 at 6:19
well i think u have to confirm from front end like are u sure want to take these plan if yes then insert .You should take this confirmation on second page where all machine snaps come. – Ajeet Singh May 16 at 6:40
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) – Bradley Hobbs May 16 at 17:42

Your Answer

 
discard

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.