M
M
misterobot4042019-07-14 07:35:18
PostgreSQL
misterobot404, 2019-07-14 07:35:18

Store 2D array in json or create table?

The database must have users, each user has its own set of cards, each card has its own set of events and config. The question arises how best to store events for the map?
I have two options :
1) storing in json

CREATE TABLE maps( 
map_id serial primary key NOT NULL,
user_id integer references users(user_id) NOT NULL,
map_name varchar (30) NOT NULL,
map_description text,
map_events json,
map_config jsonb 
);

2) creating a common table with events from which I will make a selection by card_id
CREATE TABLE maps( 
map_id serial primary key NOT NULL, 
user_id integer references users(user_id) NOT NULL,
map_name varchar (30) NOT NULL,
map_description text,
map_config jsonb 
);
/*структура события*/
CREATE TABLE events( 
event_id serial primary key NOT NULL,
map_id integer references maps(map_id) NOT NULL,
event_header varchar (30), 
event_description text, 
event_media_url varchar (100), 
event_location varchar (100)
);

My web application works with map events in the following way: receiving map events (events must be arranged in a certain sequence, so I immediately abandoned the jsonb option), changing events and their order, rewriting ALL events to the database.

Answer the question

In order to leave comments, you need to log in

2 answer(s)
P
Pasechnik Kuzmich, 2019-07-14
@Hivemaster

Create a table.

A
Andrey, 2019-07-14
@VladimirAndreev

2 tables.
Especially if there are a lot of cards, then there will be inefficient i / o, especially in early records

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question