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

  • 1
    Should I use an "association table" to link the two IDs Depends on relation type. If it is M:N - of course. If 1:N - then additional self-referencing column is enough, but additional table (with unique index by the column which stores ID on the "one" side) may be used instead of additional column too.
    – Akina
    Sep 18, 2020 at 5:31
  • @Akina useful info, thanks. What are M:N and 1:N? M:N = Many:N___? 1:N seems like 1:Many but the "N" confuses me... :) Sep 18, 2020 at 15:38
  • 1
    M:N == many-to-many. Different letters are used for to demonstrate that the amount from left and right side may differ. 1:N == 1:M.
    – Akina
    Sep 18, 2020 at 15:56

2 Answers 2


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".

  • Nice insight on the bill of materials. Originally, these were separate structures, and later were combined into one larger structure, so having two tables wasn't anticipated. I think the association table linking the two IDs will suffice for now. I plan to look into tables such as for social media sites where several users in the same table all "like" each other... :) Sep 18, 2020 at 15:41

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(


        db.ForeignKey('public.structures.id', ondelete="CASCADE"), 

        db.ForeignKey('public.structures.id', ondelete="CASCADE"), 


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(
        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'), 

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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.