Two records in the same table must be related somehow

Question

I've inherited a somewhat strange table whose records must somehow be linked to each other. In the real world, these two "structures" are combined into one larger "structure", so there should be a way of linking them.

Should I use an "association table" to link the two IDs, or is there a better way for this sort of thing? Perhaps I've answered my own question, but I thought I'd ask if there's another way...

Another option would be to delete the second record, at the same time as I add a reference to that second record as a new column, in the first record.

Maybe this is a ridiculous open-ended question for this site, but surely I'm not the first person to run into this...

Thanks, Sean

Answer 1

I believe the requirement to record "these 2+ items are used to make this 1 bigger item" is called a BOM. (Bill Of Materials).

The only set of BOM tables I've seen in action used 1 table for each depth. Your BOM would only need the two tables.

TL;DR - use the "association table".

Answer 2

I ended up using a self-referential many-to-many association table, where one structure can be related to many other structures in the same table.

In Python's Flask-SQLAlchemy library, this is how the association table is represented:

structure_structure_rel = db.Table(

    'structure_structure_rel',

    db.Column(
        'primary_structure_id', 
        INTEGER, 
        db.ForeignKey('public.structures.id', ondelete="CASCADE"), 
        primary_key=True
    ),

    db.Column(
        'secondary_structure_id', 
        INTEGER, 
        db.ForeignKey('public.structures.id', ondelete="CASCADE"), 
        primary_key=True
    ), 

    schema='public'
)

And here's the actual "structures" table, represented as a class in the SQLAlchemy object relational model (ORM):

class Structure(db.Model):
    """Create a public.structures PostgreSQL table representation 
    for use in Python Flask-SQLAlchemy
    """

    __tablename__ = 'structures'
    __table_args__ = {"schema": "public"}

    id = db.Column(INTEGER, primary_key=True)

    ...

    # Super-cool self-referential many-to-many relationship
    secondary_structure_id = db.relationship(
        'Structure', 
        secondary=structure_structure_rel,
        primaryjoin=(structure_structure_rel.c.primary_structure_id == id),
        secondaryjoin=(structure_structure_rel.c.secondary_structure_id == id),
        backref=db.backref('primary_structure_id', lazy='select'), 
        lazy='select'
    )

I can then use both those relationship "columns" (i.e. primary_structure_id and secondary_structure_id) in Flask-Admin, which is really nice.

-Sean