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
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:
- 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.)
- 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.
- 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.
- 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.)
- 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.
- 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.
- 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.
- 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.
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
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.