CODEWITHSUNDEEP
postgresql
rigorousCode
rigorousCode

Reputation: 405

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

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 ?

Upvotes: 0

Views: 1108

Answers (2)

S-Man
S-Man

Reputation: 23686

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

Upvotes: 2

user330315
user330315

Reputation:

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"?

Upvotes: 2

Related Questions