5

I'd like to create an app similar to Barbell Pro for Android, for practice / interest / educational purposes really. Or even as another example for database purposes, Fitocracy

The problem is, I have no idea how the database could be designed... For example:

  • We have a 1000 Persons using the app
  • Each Person could have 1-7 individualised WorkoutRoutines, depending on the day. (Perhaps even more -> AM workouts / PM workouts)
  • Each WorkoutRoutine has an individual set of Exercises, with some crossover e.g. someone could do Bench Press on Monday and on Friday afterall.
  • Each Exercise has a number of Sets
  • Each Set has a number of Reps

Now to me, this seems like it could potentially be a large amount of information to store for Each Person, and maybe pretty complicated to do so.

I only have some experience with relational databases and I don't know how I'd go about designing that in an efficient matter for a relational database.

I'm not asking for a design, just how I'd begin to go about it. The potential complexity is daunting for me due to lack of database experience.

Maybe it's not even that complicated, but like I say, ignorance from myself.

8

Here is a prototype database schema. It allows the creation of different exercise routines, assigns those routines to a person by date, and even has a place to log the exercises executed, and the number of reps.

enter image description here

8

Don't design your database first. If it seems daunting to try and figure out all of the variables and tables you'll need based on the initial schema, that's a sign that you may be approaching the problem from the wrong directly.

Instead, design your database when you have something to store, especially something that you'll want to summarize or query for latter. You may find out that just writing some JSON or XML to disk as text files will be all that you need. Or, you might want store a blob of XML or JSON for each routine, and allow the routines to be queried, joined, and summarized. Or, you may find that XML and JSON are more trouble that they're worth, and you really do just need a few relational tables.

If you've already got the client application down, and want to work on a database server that will store all of the information for a fitness center's clients, proceed as follows:

  1. Identify what needs to be actually storied and queried. As I alluded before, if you're not going to query on the raw data, a JSON or XML serialization of your data objects in Java may be the best bet. (Remember that, if the file is too big to fit into a CHAR, you can always store the text in a "memo" or "blob" field, depending on your database.)
  2. Look for repeated information, where more than a single field can be linked together into some inferred item -- that is, move two or more field to a separate table that would have a many-to-one relationship with where they came from. What you have listed in the question is a fair start to begin with, although you may find more or discover that you made your schema too complex.
  3. For each table, determine a correct unique key that will be immutable and unique for all records. Don't be afraid to create virtual keys via identities or similar mechanisms. And don't obsess over performance just yet; if a natural key seems better than a virtual key, go ahead and use it.
  4. Give every field and table a clear and distinct name. The names of keys should be unique throughout your database, although secondary fields do not have to be. (Although, of course, there's no harm in having them so.)
  5. With all of the above in hand, do an initial database design. You want to get some tables you can send some sample data to, and populate it with some good test data.
  6. Once the database is done, code the data access layer of your application, which will read out data in your app's native form and send said native form to some way that the database can accept.
  7. After you've got some code working and can both write appropriate data and get back the records you were expecting, load up your development database with sample data. This can be entirely fictitious, or information from your beta testers, or a combination of both. Feel free to throw a few expected year's worth of data in there. Or more.
  8. With a database whose tables have some meaty data on them, optimize your database. You may need to add or adjust keys, refactor some tables into normalized views, or tweak the data types of some fields. Numbers and testability are your friend, and should be your guide.

Don't be afraid if you find yourself going back a step, or doing them out of order. Do keep your database as provider-agnostic as you can, so if needs be you can switch from SQLite to MonoDB to MSSQL as the design demands.

Bonus Rant

Whatever you do, and I cannot stress this enough if you're not familiar with relational databases, KEEP YOUR TABLES NARROW. Don't go adding fields like set1Reps and set2Reps to the same table; that's a headache of a code-smell, and a huge sign that you should either have an internal XML or JSON field to store the asymmetric data or clear and distinct sub-tables.

If for some reason you find yourself wanting a very wide recordset so you can just loop through its fields and assign them to a single data object, you can always do so via a View and some clever SQL. But in that case, you'd be far better off with seralizing and unserializing a lot of your data object, anyway.

  • Aren't steps 1 thru 4 a necessary part of "designing a database?" – Robert Harvey Jan 31 '14 at 15:55
  • More or less. Can you think of a better way to say step 5, "actually implement steps 1 -4, such as with CREATE TABLE and whatnot"? – DougM Jan 31 '14 at 17:03
  • It's a very good answer, but you seem to be awful quick to dump XML data into a field when it should be normalized and stored correctly... – RubberDuck Mar 20 '16 at 12:08
  • Almost 5 years ago I asked this question, and this is still sage advice. Thanks @DougM – MRabRabbit Jan 3 at 9:15
2

Don't start designing from the database. You'll just lock yourself into a schema that's all wrong, once you figure out who uses it and how. Just start with requirements, write tests that prove them, write code that passes the tests, and let the schema fall out the bottom.

You might decide, after a few iterations of development, that JSON/MongoDB is better for your requirements. Or you might find an excellent schema already written for you. Or you might decide that HTML5 local storage is all you need.

For a greenfields (new) application, the schema is an implementation detail. Don't fret about it.

1

My two cents:

  • Supports trainers
  • Supports who created a plan
  • Supports who created a routine
  • An excercise plan is composed of several routines to be performed during a number of weeks
  • Each excercise routine has a preferred weekday
  • A person can execute many excercise plans during its lifetime and one excercise plan can be executed by many persons

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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