Is many to many equivalent to double one to many relation? SqlAlchemy

user_12282349 0
Viewed 86

I was wondering what is the difference between two database structures in this scenario:

Admin can edit, delete, add Manufacturers. User(Company) can select those Manufacturers, so that they are listed under their company account.

What would be the differences between A relationships(twice one to many) and B relationships (many to many) when using SQLAlchemy:

enter image description here

In B scenario Many to Many is added like this:

CompMan = db.Table('CompMan',
    db.Column('companyid', db.Integer, db.ForeignKey('company.id'), primary_key=True),
    db.Column('manufacturerid', db.Integer, db.ForeignKey('manufacturer.id'), primary_key=True)
)

So intermediate table appears automatically.

sqlalchemy sql python
2 Answers

The advantage to creating your own link table (version A), as opposed to SQLAlchemy's Association table (version B), is that with the former, you're able to add additional columns to your link table that have to do with the link itself. With the association table, there's only the relation between the two and nothing else. You wouldn't be able to get that link ID with the association table.

user_38326730

For constructing a many-to-many relationship you MUST have an intermediate relation such as CompMan.

At your example B you don't implement a many to many relationship since there is no table that stores the relation between Company and Manufacturer.

For clarification ask yourself: Assuming you hold id of any Company, how do find related manufacturers, and vice verse.

user_67576040
Related