0

I need to create a table in the database and query this table if specifically on the frontend we receive something like:

self-delivery reception: 10:00-13:00
deadline for admission: 19:00
deadline: 12:00
order acceptance:
{morning: 12:00-13:00}{evening: 16:00-17:00}

We have a structure:

Title     string                      
Value     string                       
Intervals []*Intervals

Which refers in one of the fields to another structure.

Structure:

Title     string
Value     string

I have created 3 tables:

  1. table of usual values
INSERT INTO delivery_conditions(title, val)
VALUES ('self-delivery reception', '10:00-13:00'),
       ('deadline for admission', '19:00'),
       ('deadline', '12:00');
  1. table links to another structure
INSERT INTO delivery_intervals(title, val)
VALUES ('order acceptance', 1);
  1. table for different structure
INSERT INTO customers.intervals_settings(title, val)
VALUES ('morning', '12:00-13:00'),
       ('evening', '16:00-17:00');

I also wrote a query in which I combined the fields of the third table with the json object

SELECT 
                    sfc.title, 
                    sfc.val,
                    json_build_object(
                        'title', sfi.title,
                        'val', sfi.val
                    ) AS interval
                FROM  delivery_conditions sfc
                JOIN  delivery_intervals sfi ON sfi.conditions_id = sfc.id;

as far as I understand, my method is not at all correct and I would be very grateful to people who will explain how to act in such situations.

Timothy G.
  • 6,335
  • 7
  • 30
  • 46
sweetpolir
  • 1
  • "Explain how to act in such situations" is basically asking for a relational database course, your question is too vague, and you are basically asking to design the data model for you. I suggest looking for study material about databases, learn how to design tables based on your data, try out a solution, and then, if you hit a roadblock, you make a more specific question. – Eduardo Thales Jun 16 '22 at 22:19
  • @EduardoThales Yes, I understand, thank you very much for the advice. I know how to create tables and understand the structure of relational databases, but this is the first time I encounter this situation ... I was just wondering how to act if the task is to create a table of this type – sweetpolir Jun 16 '22 at 22:23
  • What "type"? By "this situation" you mean representing a list of values? It's hard to tell what exactly is your problem. I would guess that you are in doubt of how to represent `order_acceptance`, since it can have two values instead of one, is that correct? There's many solutions, it depends on your how your data is shaped (can we have more than 2 `order_acceptance` values? Is it an unknown amount?) and how you will query. I would suggest editing your question to add actual tables, not `INSERT` statements, to describe your schema and add more details about your example. – Eduardo Thales Jun 16 '22 at 22:30
  • You might find the options posted in [this question](https://stackoverflow.com/q/54601529/11810946) (and the answers given) useful. Your approach could work (but you don't really need `json_build_object` - the question I linked provides an example). – Brits Jun 16 '22 at 22:32

0 Answers0