mysql - Inserting hierarchical related Data -

assume have systems, subsystems , devices. each of them in 0..* relationship. here tables (properties not displayed, simplification)

create table system( sid int not null auto_increment, sysident varchar(80) not null, //... cid int not null, //foreign key 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 interface sb can upload data. data structured (json) this

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

task insert or update data in tables. (maybe metadata changed device testsystem-2-90 (this ident not unique in tables).

approach :

insert system(sid, sysident, cid) values  (  (select sid (select distinct sid user   join customer on user.cid = customer.cid join system   on system.cid = customer.cid   customer.cid = 1 , system.sbid = 1 a), 'systemsname', 1) ) on duplicate key update // metainfo 

is there way make simpler? system, no problem insert/update properly. subsystems , devices getting huge , i'm cautious approach.

any advice?

i not sure understood joins properly, not think should go queries nested deeply.

get request client, parse , move least generic element there.

so, example above need execute several queries:

1. select sid system name='testsystem'
2. select subsid subsystem id=2
3. ........

btw, not sure understood intervals in json correctly ('"2-90"').

this approach simplier understand. if have performance issues, need dealt separately. think caching speed things orders of magnitude more, use of complicated queries.

if file parsing contains more several entries, can use local cache - map system names sids example etc.