Two Dimensional Database Design -SQL or JSON

Question

I need a database design advice. I am currenty using SQLite ( or i can switch to any other database for those tables) I have A,B,C,D columns with rows from 24 to 1008 . X represents whether the column empty or not.

my first idea-> I can do Table A,B,C,D with 24 to 1008 (which seems dumb)

second idea -> something like table X (which has id(FK) to another table where I will be getting report ID) which has 24 to 1008 and values will be A,B,C or D so in this case an example query is : SELECT A from table X JOIN table_report ON table.report.id=X.id where table_report.id=3;

so with 4 queries I will be getting values for A,B,C and D however I am confused a lot now

Efficiency is not a big deal atm however I don't want to design a dumb thing. I will be filling HTML tables with those data ( Express+ejs)

I am open for any advice

https://i.hizliresim.com/PO7PV5.png

Answer 1

It's impossible to give a best design with the limited info you've provided. But it's likely you need to study some of the principles of RDB design.

The natural way of representing what you show in the image would be as a table of unique pairs, where each pair denotes one entry with an X:

CREATE TABLE schedule (
  hour INTEGER PRIMARY KEY,
  tag TEXT PRIMARY KEY
);

Note I'm guessing a name of "hour" for the numerical horizontal axis and "tag" for the vertical A - D axis, since you didn't mention what these values actually are.

You'd enforce the additional data constraints with triggers:

CREATE TRIGGER validate_schedule_insert BEFORE INSERT ON schedule
BEGIN
  SELECT CASE 
    WHEN NEW.hour NOT IN (24, 48, ...) OR NEW.tag NOT IN ("A", "B", "C", "D")
    THEN RAISE (ABORT 'Bad pair')
  END;
END;

The maximum number of entries in the table would be 4 * 42 = 164. You'd check whether a given entry has an X with:

SELECT * FROM schedule WHERE hour = ? AND tag = ?;

This returns 1 or 0 results for X or no X respectively.

Of course you can learn all the tags with an X for a given hour with a single query:

SELECT tag FROM schedule WHERE hour = ?;