How to design my PostgreSQL database with a list of objects?

Question

I'm trying to build my PostgreSQL database.

I receive data like this from another source :

Runner

id : int
Name : string
Age : int
Steps : List of Step

My question come with the Steps Values. It's a list of Steps (specific of each runner) that I will have to update (add step in the list, update old steps, etc).

The step object look like this

Step

id : int
Name : string
Date : date
isFinished : boolean

What is the best way to design my database with that list of steps ? Another table or something like a JSON object in the runner table ?

user330315 · Accepted Answer · 2021-03-16 09:18:32Z

2

If a step is specific to each runner, then you have a text-book example of a one-to-many relationship. Just add a foreign key from step to runner and get rid of the steps in the runner table:

create table runner 
( 
  id    integer primary key, 
  name  text not null,
  age   int not null, --<< age shouldn't be stored, rather the date of birth
);

create table step
(
  id           integer primary key, 
  name         text not null, 
  "date"       date not null, 
  is_finished  boolean not null default false,
  runner_id    integer not null references runner
);

Note that date is a horrible name for a column. For one because it's a keyword, but more importantly it doesn't document the meaning. Is that the "planned date"? The finish date"? The "regisration_date"?

answered Mar 16, 2021 at 9:18

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

rigorousCode Over a year ago

Thanks a lot for your answer and the convention tips! It will help me.

kometen Over a year ago

You can use created_at for the "date" column @MickaëlMorgand.

S-Man · Accepted Answer · 2021-03-16 09:26:44Z

2

Normalize your database. That means, you need a steps table. This enables you to do updating, inserting, deleting operations on specific records without affect unrelated records. Furthermore you can create indexes on this table, add attributes (columns) if necessary, ...

If you will need a JSON object later, it is always possible to create it anytime.

So:

CREATE TABLE steps (
    id int,
    name text,
    step_date date,
    is_finished boolean,
    runner_id int REFERENCES runner(id)
)

The foreign key to link the steps records to their related runner records requires the id column from table runner to be the PRIMARY KEY:

CREATE TABLE runner (
    id int PRIMARY KEY,
    ...
)

edited Mar 16, 2021 at 9:26

answered Mar 16, 2021 at 9:18

S-Man

24k9 gold badges51 silver badges78 bronze badges

1 Comment

rigorousCode Over a year ago

Perfect, I will do like this. Thank you!

Collectives™ on Stack Overflow

How to design my PostgreSQL database with a list of objects?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related