Combination of non-unique foreign keys to create primary key?

user_14543774 0
Viewed 150

I am trying to create a table in SQLAlchemy which would connect a movie and a category and give it a score (The Shining in the category Thriller might have a score of 700 whilst The Shining in the category Drama might have a score of 540 etc).

This means that the table will include many references to The Shining and many references to Thriller but only one case where it is the combination of The Shining and Thriller which would also include the score of 700. This picture explains it well.enter image description here

Category and Movie are classes of their own.

So searching for an answer I found that I could have multiple primary keys but that wouldn't work in this case since primary keys have to be unique and the Movie ID and Category ID aren't unique.

I found the word "Composite Key" which sounds like what I'm looking for but demonstrated in SQLAlchemy it doesn't seem to do the same thing as I thought it would.

How would I go about making a table that does what I'm looking for? AKA the "Category Movie Scores" from the picture? And the reason I'm not just creating a regular ID that would serve as a primary key is that it would never ever be used, I'm only everu querying this table with the Movie and Category ID's.

Thanks in advance!

sqlalchemy python database
1 Answers

This can be resolved with an Association Object. Which is a Many-To-Many relationship based on an object to be able to store additional data.

class Association(Base):
    __tablename__ = 'association'
    movie_id = Column(Integer, ForeignKey('movie.id'), primary_key=True)
    category_id = Column(Integer, ForeignKey('category.id'), primary_key=True)
    score = Column(Integer)
    votes = Column(Integer)
    category = relationship("Category", back_populates="movies")
    movie = relationship("Movie", back_populates="categories")

class Movie(Base):
    __tablename__ = 'movie'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))
    categories = relationship("Association", back_populates="movie")

class Category(Base):
    __tablename__ = 'category'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))
    movies = relationship("Association", back_populates="category")

Use sqlite_autoincrement=True with sqlite to create the autoincrement on database level.

Before you can append a category to the movie it needs to be associated with an association instance:

# create movie, append a category via association
m = Movie()
a = Association(score=700, votes=0)
a.category = Category()
m.categories.append(a)

# iterate through category objects via association, including association
# attributes
for assoc in m.categories:
    print(assoc.score)
    print(assoc.category)
user_23458700
Related