Inserting hierarchical related Data

Question

Assume we have systems, subsystems and devices. Each of them are in 0..* relationship. Here are the tables (properties are not displayed, for simplification)

CREATE TABLE system( sid INT NOT NULL AUTO_INCREMENT, sysident VARCHAR(80) NOT NULL, //... cid INT NOT NULL, //Foreign Key to Customer PRIMARY KEY(sid)); CREATE TABLE subsystem( subsid INT NOT NULL AUTO_INCREMENT,//... subsident INT NOT NULL, sid INT NOT NULL, //systems foreign key PRIMARY KEY(subsid)); CREATE TABLE device( did INT NOT NULL AUTO_INCREMENT,//... dident INT NOT NULL, subsid INT NOT NULL, //subsystems foreign key PRIMARY KEY(did));

Now assume there's a interface where sb can upload data. Data is structured (JSON) like this

{ "systemname": "testsystem", //system.systemname "deviceinfos": [ { "id": "2-90" //"subsystem.subsystemident-device.deviceident" }] }

Task is to insert or update the data in the tables. (maybe some metadata changed for the device testsystem-2-90 (this ident is not unique in the tables).

Approach is :

INSERT INTO system(sid, sysident, cid) VALUES ( (SELECT sid FROM (SELECT DISTINCT sid FROM user JOIN customer on user.cid = customer.cid JOIN system ON system.cid = customer.cid WHERE customer.cid = 1 and system.sbid = 1 as a), 'systemsname', 1) ) ON DUPLICATE KEY UPDATE // some metainfo

Is there a way to make this simpler? For system, its no problem to insert/update this properly. But for subsystems and devices its getting huge and I'm cautious about the approach.

Any advice?

Vladislav Rastrusny · Accepted Answer · 2015-11-17 12:12:31Z

I am not sure I understood your joins properly, but I do not think you should go with queries nested so deeply.

Get request from the client, parse it and move from the most to the least generic element there.

So, for the example above you need to execute several queries:

SELECT sid from system WHERE name='testsystem'
SELECT subsid FROM subsystem WHERE id=2
........

BTW, I am not sure I understood intervals in your json correctly ('"2-90"').

This approach is simplier to understand. If you will have performance issues, then those will need to be dealt with separately. I think caching will speed up things orders of magnitude more, then the use of complicated queries.

If the file you are parsing contains more than several entries, you can use something like local cache - a map from system names to sids for example etc.

Collectives™ on Stack Overflow

Inserting hierarchical related Data

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related