正確組織數據庫結構和選擇

對不起新手問題。

例如，有某種抽象設備，我們將呼叫設備類及其任務：Task1、Task2、Task3。具體而言，我將用 java 程式碼描述所有內容。

class Device {
long id;
String name;
int ip;
int filed1;
String filed2;
// and so on
}

class Task1 {
long id;
long device_id; // id from Device class
int type = 1;
int property1;
char property2;
byte property3;
// Properties and their number are completely different in all Task, that is, the tasks are completely different.
}

class Task2 {
long id;
long device_id; // id from Device class
int type = 2;
String anotherProperty1;
float anotherProperty2;
long anotherProperty3;
short anotherProperty4;
double anotherProperty5;
int anotherProperty6;
// Properties and their number are completely different in all Task, that is, the tasks are completely different.
}

class Task3 {
long id;
long device_id; // id from Device class
int type = 3;
Charsequence yetAnotherProperty1;
AnotherType yetAnotherProperty2;
// Properties and their number are completely different in all Task, that is, the tasks are completely different.
}

此外，為每個設備分配了一定數量的任務，既有不同的類別，也有相同的類別。例如。

// pseudocode
Device device1 = new Device (.....);
// The list of tasks for the device 1 (Any combination, any number)
1. new Task2
2. new Task2
3. new Task1
4. new Task3

Device device2 = new Device (.....);
// The list of tasks for the device 2 (Any combination, any number)
1. new Task1
2. new Task3

Device device3 = new Device (.....);
// The list of tasks for the device 3 (Any combination, any number)
1. new Task1
2. new Task3
3. new Task1
4. new Task3
5. new Task1
6. new Task3
7. new Task1
8. new Task3
9. new Task1
10. new Task3
// and so on

我創建了包含所有必填欄位的設備表，但我不知道如何將所有這些正確保存在數據庫中。我會假設：

• 是否需要創建更多表：Task1、Task2、Task3？
• （保存時）將所有任務、Device.id 對應於 Task.device_id 及其執行順序放到特定表中？
• （載入時）從所有表 Device、Task1、Task2、Task3 進行查詢 - 並從每個任務中檢查 Device.id = Task1.device_id AND Device.id = Task2.device_id AND Device.id = Task3.device_id？

首先，假設是正確的，數據庫結構的組織是正確的還是……？

如果是，如何正確地從多個表中進行優化選擇，如果可能的話，編寫 sql 請求..

我正在使用 sqlite

我想你想要的是要麼

• 實體值表
• 無模式列

只需使用JSONB（json 的二進制和可索引儲存），您就可以在 PostgreSQL 中做您想做的事情。

CREATE TABLE device (
 device_id  int   GENERATED BY DEFAULT AS IDENTITY,
 name       text
);
CREATE TABLE task (
 task_id    int   GENERATED BY DEFAULT AS IDENTITY,
 device_id  int   REFERENCES device,
 type       int,
 properties jsonb
);
CREATE INDEX ON task(device_id, type);

INSERT INTO device (name) VALUES ('foobar')
INSERT INTO task (device_id, type, properties) VALUES
 (1, 1, '{"property2": "foo", "property3": "foobar"}'),
 (1, 2, '{"another property": "qux", "another property2": "quux"}')
;

引用自：https://dba.stackexchange.com/questions/195356