CODEWITHSUNDEEP
sqldatabasepostgresqldatabase-design
Slaknation
Slaknation

Reputation: 1926

SQL Database Design: How to get around storing a list?

So I have a database table that needs to store a list of exercises for each routine like so: enter image description here

I know that you are not supposed to use lists for SQL databases but I honestly have no idea how else I would do that. Any ideas?

Upvotes: 0

Views: 333

Answers (2)

FXD
FXD

Reputation: 2060

The standard SQL way to do that is with 2 tables

create table routine (
    id serial primary key,
    name text
);

create table exercises (
    routine_id integer references routine(id) on delete cascade /*optional*/,
    exercise text
);

Postgres gives a nice syntax (although not 100% standard) to create routines with exercises in 1 go:

with new_routine_id as (
    insert into routine(name)
    values ('Chest')
    returning id
)
insert into exercises
select id, unnest(array['Bench Press', 'Skull Crusher', 'Incline Bench Press'])
from new_routine_id

Selection is just as you would expect:

select id, name, array_agg(exercise order by exercise /*it could be ordered using an additional field*/) as exercises
from routine
join exercises on id = routine_id
group by id, name

Edits @klin has posted an alternative solution with 1 table containing an array for the exercises directly. The solution is perfectly valid and in some regard, simpler than mine (I quickly mentioned this solution in my first comment after all).

IMHO, there is 1 reason why a solution would be "better" than the other. Do you work in a team where everybody knows how to handle arrays?

  • YES: pick @klin's solution
  • NO: pick my solution above. You can do everything you need with only regular JOIN, no arrays.

Upvotes: 2

klin
klin

Reputation: 121634

I know that you are not supposed to use lists for SQL databases...

This is not obvious in modern databases. Arrays in Postgres are really well implemented and may make life easier. There is nothing wrong in the relational approach described by @lau but you can also consider a simple structure like this

create table routines (
    id serial primary key,
    name text,
    exercises text[]
);

Please, read Is it bad design to use arrays within a database? to get more information that may facilitate making the right decision. Also, pay attention to this tip from the documentation

Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

Upvotes: 1

Related Questions