How can I have duplicate entries in a many-to-many relationship.

[Jackson, Cameron]

A have a Machines table and a Parts table. A machine is made up of many parts, and each part could be in many machines, so clearly this is a M:M relationship. I've implemented this with a secondary table, which has two columns: machine_id and part_id. There is no primary key or unique constraint on the secondary table, just two foreign keys.

 

So far so good.

 

The problem is when I want to have 2 of a part in a machine. It was no problem doing this:

 

     machine = Machine('theMachine')

     machine.parts.append(session.query(Part).filter_by(name = 'thePart').first())

     machine.parts.append(session.query(Part).filter_by(name = 'thePart').first())

     session.add(machine)

 

I now have two identical rows in the secondary table, which is what I want. However, when I try to retrieve the object:

 

     print session.query(Machine).filter_by(name = 'theMachine').first().parts

 

I just get:

 

     InstrumentedList: [<Part(u'thePart')>]

 

The duplicates are being cut out, which is very annoying. This Stack Overflow question seems relevant, but I wasn't to glean any useful information from it.

 

How can I have duplicate entries in my M:M relationship?

 

Cheers,

Cam

 

Cameron Jackson
Engineering Intern
Air Operations

Thales Australia
Thales Australia Centre, WTC Northbank Wharf, Concourse Level,
Siddeley Street, Melbourne, VIC 3005, Australia
Tel: +61 3 8630 4591
cameron...@thalesgroup.com.au | www.thalesgroup.com.au

------------------------------------------------------------------------- DISCLAIMER: This e-mail transmission and any documents, files and previous e-mail messages attached to it are private and confidential. They may contain proprietary or copyright material or information that is subject to legal professional privilege. They are for the use of the intended recipient only. Any unauthorised viewing, use, disclosure, copying, alteration, storage or distribution of, or reliance on, this message is strictly prohibited. No part may be reproduced, adapted or transmitted without the written permission of the owner. If you have received this transmission in error, or are not an authorised recipient, please immediately notify the sender by return email, delete this message and all copies from your e-mail system, and destroy any printed copies. Receipt by anyone other than the intended recipient should not be deemed a waiver of any privilege or protection. Thales Australia does not warrant or represent that this e-mail or any documents, files and previous e-mail messages attached are error or virus free. -------------------------------------------------------------------------

[Michael Bayer]

On Dec 19, 2011, at 8:13 PM, Jackson, Cameron wrote:

A have a Machines table and a Parts table. A machine is made up of many parts, and each part could be in many machines, so clearly this is a M:M relationship. I've implemented this with a secondary table, which has two columns: machine_id and part_id. There is no primary key or unique constraint on the secondary table, just two foreign keys.

 

So far so good.

 

The problem is when I want to have 2 of a part in a machine. It was no problem doing this:

 

     machine = Machine('theMachine')

     machine.parts.append(session.query(Part).filter_by(name = 'thePart').first())

     machine.parts.append(session.query(Part).filter_by(name = 'thePart').first())

     session.add(machine)

 

I now have two identical rows in the secondary table, which is what I want. However, when I try to retrieve the object:

 

     print session.query(Machine).filter_by(name = 'theMachine').first().parts

 

I just get:

 

     InstrumentedList: [<Part(u'thePart')>]

 

The duplicates are being cut out, which is very annoying. This Stack Overflow question seems relevant, but I wasn't to glean any useful information from it.

 

How can I have duplicate entries in my M:M relationship?

many-to-many assumes <left_id>/<right_id> are unique.   Those cols should be a primary key, or there should otherwise be another column on the table that's primary, which would turn it into an association.     

The fact that you have no other information stored with each <left_id>/<right_id>, but you'd like two of them, violates normal form.  Actually first normal form.  (background, http://en.wikipedia.org/wiki/First_normal_form )

At minimum, the additional information you might want to associate with each association between machine and part is how many of them are present, meaning there would be just one row between part and machine.  Or perhaps a simple index (a counter for each one).    But in relational databases, all rows should express something unique that isn't anywhere else.

 

Cheers,

Cam

 

Cameron Jackson
Engineering Intern
Air Operations

Thales Australia
Thales Australia Centre, WTC Northbank Wharf, Concourse Level,
Siddeley Street, Melbourne, VIC 3005, Australia
Tel: +61 3 8630 4591
cameron...@thalesgroup.com.au | www.thalesgroup.com.au

------------------------------------------------------------------------- DISCLAIMER: This e-mail transmission and any documents, files and previous e-mail messages attached to it are private and confidential. They may contain proprietary or copyright material or information that is subject to legal professional privilege. They are for the use of the intended recipient only. Any unauthorised viewing, use, disclosure, copying, alteration, storage or distribution of, or reliance on, this message is strictly prohibited. No part may be reproduced, adapted or transmitted without the written permission of the owner. If you have received this transmission in error, or are not an authorised recipient, please immediately notify the sender by return email, delete this message and all copies from your e-mail system, and destroy any printed copies. Receipt by anyone other than the intended recipient should not be deemed a waiver of any privilege or protection. Thales Australia does not warrant or represent that this e-mail or any documents, files and previous e-mail messages attached are error or virus free. -------------------------------------------------------------------------

-- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Jackson, Cameron]

Makes sense I suppose. I think I'll add a quantity column.

 

Thanks.

 

Cameron Jackson
Engineering Intern
Air Operations

Thales Australia
Thales Australia Centre, WTC Northbank Wharf, Concourse Level,
Siddeley Street, Melbourne, VIC 3005, Australia
Tel: +61 3 8630 4591
cameron...@thalesgroup.com.au | www.thalesgroup.com.au