Beyond Many-To-Many

24 May 2011

As I work through this project, I will be sharing some of what I think are interesting choices that I make. I have started building out the model classes as outlined in The Basic Schema. The Song–Creator relationship was the first to be built.

In building these models, it became clear that the original thought was not quite clear. First, we have unneeded redundancy with the Creator and Person models, so we can bypass the Creator entities altogether. Now that we no longer have the one-to-one proxy between Song_Creator and Person, we can rename Song_Creator to the more terse, Creator. I hope I haven’t lost you here.

The Song, Person, and CreatorType entities were easy to define, so I won’t go into any detail there. The fun is in the Creator entity. There are a couple of things I want to take note of here:

• There is a three dimensional many-to-many(-to-many) relationship here.
• The primary key is a composite of the three foreign keys.

class Creator(Base):
    __tablename__ = "creator"

    primary = Column(Boolean)
    song_id = Column(Integer, ForeignKey('song.id'), nullable=False,
                     primary_key=True)
    creator_type_id = Column(Integer, ForeignKey('creator_type.id'),
                             nullable=False, primary_key=True)
    person_id = Column(Integer, ForeignKey('person.id'), nullable=False,
                       primary_key=True)

    song = relation('Song', backref=backref('creator', order_by=id))
    creator_type = relation('CreatorType',
                            backref=backref('creator', order_by=id))
    person = relation('Person', backref=backref('creator', order_by=id))

Now, I am in no way an expert database administration, so I have no idea if any of this is really the right way to model this, but it looks like it will work. Please feel free to take a look or share your thoughts.