locked
advice on db design for saving 'dynamic structures' RRS feed

  • Question

  • User1655654435 posted

    Hi,

    i'm making a node-like structure in my app that i need to save data from in a database.

    by 'node-like' i mean that one node(class with properties) can have one or more nodes associated. then those associated nodes can further have one or more nodes etc.

    this would be a tree-like structure with data that i need to save somehow, but i'm unsure to how because this is dynamic; it's not set how many nodes there are.

    the app is a test that will be answered by company departments and it's under-departments(if any). this means that i need to keep track of the hirarchy when saving in the db in order to sort the results(i need to know the result in one department, but also that department and it's higher or lower departments.) 

    the data pr department/class/node is actually very simple, just text or number properties.

    So in constructing a database structure for this, i can't just create a company table, then have a one to many departments and then have one to many more apartments. because that would be a loop. i could just create tables that might or might not be used, like department have one or more departments2, which have one or more departments3 etc.

    i'm at a loss in how to do this, any help  would be much appreciated.

    thankyou

    Thursday, June 27, 2019 7:25 AM

All replies

  • User475983607 posted

    Unclear... The object model must be known, at some point, otherwise there is not way to know what object you have.   The object model then translates to a schema design using relationships which handles the depth of the relationships.

    It sounds like you need a table with a self or recursive  join.

    https://stackoverflow.com/questions/1757260/simplest-way-to-do-a-recursive-self-join

    But, you can save the data as JSON or XML.  SQL server has functions to handle XML and JSON (depending on the SQL version).

    Thursday, June 27, 2019 1:52 PM