Bdtjtk

I am creating an android app in java that uses sqlite to store data. The data i want to store are gym entities like exercise name, set, rep , weight and time.

I have problem whether i create one table for all exercises, or each exercise have a table like Chest bench press table , incline bench press table etc.

Is it better to design the database to create one table for all exercises or each exercise have it`s own table ?

Also is it good to allow the user to create tables dynamically? ( because when i allow the user to create tables dynamically the name of the table should not have spaces and i don`t know how to solve this problem)

Normalisation is one of the key concepts to database design and the tables therein, that is to minimise/eliminate duplication of data (especially textual data which storage and processing wise is inefficient).

So basically the answer is YES you should have multiple tables BUT NO you should not really have dynamically assigned tables on a per user basis, a single user table along with mapping users to the exercises done is what would typically be used.

• If the number of users and or exercises became very large (10's perhaps 100's of thousands) then you may then need to consider some form of separation.

By the sound of it you have a table for the exercises, a table for users and then a table that records(logs) the exercises undertaken which would map to the user and the exercise.

As an example consider the following :-

DROP TABLE IF EXISTS user;

DROP TABLE IF EXISTS exercise;

DROP TABLE IF EXISTS log;



CREATE TABLE IF NOT EXISTS user (user_id INTEGER PRIMARY KEY, user_name TEXT, user_password TEXT);

CREATE TABLE IF NOT EXISTS exercise (exercise_id INTEGER PRIMARY KEY, exercise_name TEXT);

CREATE TABLE IF NOT EXISTS log (user_id_reference INTEGER, exercise_id_reference INTEGER, started TEXT DEFAULT CURRENT_TIMESTAMP, finished TEXT DEFAULT 0, repititions INTEGER, weight REAL);



INSERT INTO user (user_name, user_password) VALUES ('Fred','passwordFred'),('Mary','passwordMary'),('Linda','passwordLinda');



INSERT INTO exercise (exercise_name) VALUES ('Goblet Squat'),('Farmer''s Walk'),('Bent-Over Row'),('2 Armed Stiff-Legged DeadLift');



INSERT INTO log VALUES

    (2,3,'2018-12-31 10:30','2018-12-31 10:45',100,5.5),

    (2,1,'2018-12-31 10:50','2018-12-31 11:00',50,7.5),

    (2,3,'2019-01-01 10:25','2019-01-01 10:30',75,5.5),

    (1,1,'2018-12-30 09:00','2018-12-30 09:10',60,6.5),

    (1,1,'2018-12-31 09:00','2018-12-31 09:10',60,6.5),

    (1,1,'2019-01-01 09:00','2019-01-01 09:10',60,6.5);





SELECT sum(repititions * weight) AS workdone, user_name, exercise_name 

FROM log

JOIN exercise ON exercise.exercise_id = log.exercise_id_reference

JOIN user ON user.user_id = user_id_reference

GROUP BY user_id, exercise_id

ORDER BY user_name;

This creates 3 tables :-

1. 
   user for the user specific information that is singular to the user.


2. 
   exercise for each exercise that can be undertaken


3. 
   

   log for each exercise per use that is undertaken.

   
   
   
   
   

   • Note that user and exercise have an id column defines with a type INTEGER PRIMARY KEY, this has a special meaning in SQLITE in that it makes the column an alias of the special/normally hidden rowid and that id a value is not provided then the value will be a unique identifier of the row (1 for the first, probably 2 for the next and so on). These are the most efficient means of identifying a row and thus are used by the log table to reference the user and the exercise.

   
   

   • log is what some term as a mapping/reference table an facilitates a many to many relationship additionally values specific to the log such as the date/time started and ended the weights used and the repetitions are stored in the log.

   
   
   
   

After the tables are created data is added to the 3 tables (3 users, 4 exercises and some log entries)

Finally a query is run to accumulate the weights * the repetitions per user per exercise as an example of tying all of this together. The result being :-

• sum is what is termed as an aggregate function. An aggregate function will consider all the values per group (as defined by the GROUP BY keyword). In the case of sum all the values will be added together for each group.




• JOIN is used to combine the tables according to an expression (relationship).

First, you can draw an ER diagram for your application and then convert it to tables. The number of tables depends on your use case but generally, there are more than one tables in an application. Try to apply database normalization rules to your tables. https://en.wikipedia.org/wiki/Database_normalization

Generally, there is no need to let the user create a table you can create a table yourself and get data (rows) from users.

Normalization often involves considering all the trade-offs. Having a table for each exercise might be cleaner, but you also have to consider the possible drawbacks. Do you already know the full list of exercises that your App will have to manage? If you put too many exercises from the beginning the users might not like it, having to scroll a lot to search the right one. On the other hand you might disappoint the users by not putting the exercises they like. Creating a table for each one might force to update frequently your App adding or removing exercises. If you are asking about user defined tables I guess that you are even considering the possibility of letting the users define their own exercises. So a fixed number of tables might constrain your app.

You could create tables with generic names like exercise1, exercise2, ..., and so on and then use a mapping tables to join exercise name and exercise data, but it could be slow.

I advise you to try and make it as dynamic as possible, but to prevent performance issues you should make a rough estimate of how many records a user might generate using the app and make some small test app to try the performance of different designs (just insert some dummy data and run some queries on a smartphone).