Several boolean fields or one text field in database?

Question

I'm just getting my feet wet in databases and in creating a database I came to the realization that there are instances where what could be a text field could also be several Boolean fields.

To give a concrete example: I'm creating a database that has various exercises and the muscles that each exercise works. For each exercise, I could either have one muscles_worked TEXT or shoulders BOOLEAN, pecs BOOLEAN, lower_legs BOOLEAN, etc.

I'm trying to be smart about system resources and keep the database (as well as any attached code) as lean as possible, so which would be the preferred method?

Thanks in advance.

SQLite doesn't have a native boolean class, but you can store boolean values as an integer. 0 or 1. I would recommend you go the latter, because how are you going to index your muscles_worked TEXT? If you are going to query later for these values, you'll want them to be separate columns. With that said, I've worked in situations where I knew I wasn't ever going to index or query that value in a where clause, but I absolutely needed to save space - you can store multiple booleans in a single byte http://blog.millermedeiros.com/using-integers-to-store-multiple-boolean-values/ — Robert Dupuy, Sep 19 '15 at 20:15
Sorry, perhaps I should have clarified, I am using LibreOffice Base to compile the data set and will be converting it later to SQLite. Again, just getting started with databases, so thanks for the information, i'll make sure to remember that when I move it to SQLite. — IllWills, Sep 19 '15 at 21:14

score 2 · Answer 1 · answered Sep 21 '15 at 06:53

One method that will work with any relational DBMS becomes obvious if you use the language of relations.

"I have these exercises and those muscles. How can I form a relationship between them to show which muscles are worked by an exercise and which exercises works each muscle." Then when you explain that "each exercise may work several muscles and each muscle may be worked by several exercises" then the answer jumps right out at you.

A standard intersection or junction table:

create table Exercises(
    ID      int  not null,
    Name    varchar( 16 ) not null, -- 'Bench Press', 'Chest Fly', etc.
    ...,
    constraint PK_Exercieses primary key( ID )
);
create table Muscles(
    ID      int  not null,
    Name    varchar( 16 ) not null, -- 'Pectorals', 'Biceps', etc.
    ...,
    constraint PK_Muscles primary key( ID )
);
create table ExerciseToMuscle(
    ExerciseID int  not null,
    MuscleID   int  not null,
    ...,
    constraint PK_ExerciseToMuscle primary key( ExerciseID, MuscleID ),
    constraint FK_ExerciseToMuscle_Exercise foreign key( ExerciseID )
        references Exercises( ID ),
    constraint FK_ExerciseToMuscle_Muscle foreign key( MuscleID )
        references Muscles( ID )
);

You can add attributes to the intersection table to describe the relationship. Such an attribute might be Intensity, to show just how much an exercise "hits" the muscle.

ExerciseID MuscleID Intensity
        17        4         5
        17        3        10

This would show that Exercise 17 hits muscle 3 with a maximum "burn" of 10, but muscle 4 with only a 5. This allows you to answer questions like "Which exercises are best for my quads?"

Several boolean fields or one text field in database?

1 Answers1