1

Sorry for newbie question.

For example, there is some kind of abstract device, we will call the Device class and the tasks for it: Task1, Task2, Task3. For concreteness, I will describe everything in java codes.

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.
}

Further, for each device a certain number of tasks are assigned, both with different classes and with the same ones. For example.

// 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

I created the Device table with all the required fields, but I do not know how to save all this correctly in the database. I will assume that:

  • is need to create more tables: Task1, Task2, Task3?

  • (when saving) to the specific tables put the all tasks, Device.id which corresponds to Task.device_id and the order of their execution?

  • (when load) do a query from all the tables Device, Task1, Task2, Task3 - and check from each task whether Device.id = Task1.device_id AND Device.id = Task2.device_id AND Device.id = Task3.device_id?

First, is the correct assumption, is the organization of the database structure correct or ....?

If yes, how correctly to do the optimized selection from several tables, write sql request if it is possible ..

I'm working with sqlite

1
  • You’ve not explained enough, but it seems that if you meant each entity can have any number of “property”values assigned, those should be moved into a separate child table. For example, a person table should have a child table phone for zero, one, or more phone numbers (work, home, day, night, mobile, landline, in-case-of-emergency, etc.). Jan 16 '18 at 6:48
1
+100

I think what you want is either an

  • Entity-Value Table
  • Schemaless column

You can do what you want in PostgreSQL simply with JSONB (a binary and indexable store for json).

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"}')
;
4
  • Your answer agrees with my decision. Thank you. But I'm working with sqllite. Perhaps i can do this like: CREATE TABLE task (task_id int GENERATED BY DEFAULT AS IDENTITY, device_id int REFERENCES device, type int, properties text, SequenceNr int); ?
    – ilw
    Jan 18 '18 at 8:59
  • @Andreyua SQL Lite should support the above just add JSON1 to it. Much better than using text to describe the properties. Jan 19 '18 at 19:25
  • What about sqlite for android?
    – ilw
    Jan 20 '18 at 8:51
  • @Andreyua not sure, you can certainly use JSON1 on android but I guess it doesn't ship with it. github.com/requery/sqlite-android/tree/master/sqlite-android/… You may consider just storing the properties as JSON for forwards compatibility, seems pretty likely they'll get around to it some day. Jan 21 '18 at 17:46
2

First of all: It is necessary to create one table for each task?

In my opinion: No

You can create only one table "Tasks" where you define a name for your Task and define your standard properties of a task. Whenever you want to create more tasks, then you need to create new tables every time.

As an example: If you want to save humans, you don't need to create tables like "female_childs", "males", "females", "died_females" or whatever - Just create one table "persons" where you can store stuff like "gender", "alive", "birthDay", "deathDay"...

Your Question: Order of execution In this case, what you need is some sort of sequence to order your tasks. This can be stored in a new table alongside with your device, like that:

  • DeviceId
  • TaskId
  • SequenceNr

I think I've got your confusion about these dynamic task properties but as Basil Bourque describes, you need a "child" property table to store information like you want to do it.

Your third question is now obsolete :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.