jeudi 12 mars 2020

SQL (PostgreSQL): design suggestion for multiple references to same (non-represented) object acting as siblings and sharing children efficiently

To describe the question in the simplest form: is there a recommended relational SQL design to allow for multiple rows of the same table to be 'linked' in such a way that, by obtaining one reference we can find all the other 'linked' references easily?

Say that an object (which is not independently represented so we can't have a table relationship between object_table and references_table) can have multiple independent references which are discovered separately. We know that REF_A (row 1) refers to the same (non-represented) object REF_B (row 2), so when obtaining REF_A we are able to easily retrieve REF_B. There may also be REF_C, REF_D...

The relationship between REF_A and REF_B musn't be a parent/child relationship because one is not a category of the other: REF_A and REF_B are siblings, not hierarchically aligned.

In addition, REF_A may have children (same table with parent/child relationship) SUB_REF_A1, SUB_REF_A2, etc. and I would like to have those children somehow shared among siblings REF_A, REF_B... maybe not indistinguishably, but with ease of access: from REF_B, easily obtain children of REF_A. Noting that an equivalent phenomenon may happen among children: SUB_REF_A1 may refer to same sub-object SUB_REF_B1, which should be treated as siblings (and not cousins such as SUB_REF_A1 and SUB_REF_A2).

Finally, REF_A and REF_B must not share the same row in the table (I could have gathered all siblings them under an ARRAY column) because REF_A and REF_B would have other specific properties that need to be preserved separately.

The objective could thus be described as establishing a multi-row association/link between REF_A, REF_B, REF_C... of a same table.

At my level of understanding, it is easy to represent in RDB a link between A and B (such as parent/child relationship or via a separate table binding ID_A and ID_B), but I don't know what pattern to use to establish a multi-lateral link between A, B, C, ... in particular when the number of elements is not known, and when the elements are accumulated progressively.

BTW I'm using SQLAlchemy ORM in Python with PostgreSQL.

(Hope this is clear, although I feel that I may need to refine the question in the morning)

Aucun commentaire:

Enregistrer un commentaire