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