SO told me to ask here, so this is just copy-pasted from over there
We're making a database of anime. Every anime can be listed as having been made by an arbitrary number of studios, and every studio can have made an arbitrary number of anime.
The setup we're using to store the data is like this:
class Anime(Base):
__tablename__ = 'anime'
uid = Column(Integer, Sequence('anime_id_seq'), primary_key=True)
studios = relationship("StudioEntry", backref="anime")
class StudioEntry(Base):
__tablename__ = 'studio_entries'
uid = Column(Integer, Sequence('studio_entry_id_seq'), primary_key=True)
anime_id = Column(Integer, ForeignKey('anime.uid'), nullable=False)
studio_id = Column(Integer, ForeignKey('studios.uid'), nullable=False)
class Studio(Base):
__tablename__ = 'studios'
uid = Column(Integer, Sequence('studio_id_seq'), primary_key=True)
name = Column(String)
Where each StudioEntry matches a Studio to an Anime.
This by itself is fine, however there's many kinds of similar data we have to store (think licensors, publishers, genres). Ideally, we'd like to replace the {Studio,Licensor,...}Entry classes (and tables) with one "MetadataEntry" class. What would be the best way of going about this, and is this even advisable to begin with?
Answers to questions in comments:
@mustaccio It just seems redundant to have so many tables for what is essentially one thing. I can't really give a concrete reason why I think it's bad, just that it feels like it's not the right thing to do.
EAVitis
coming on. This absolutely horrible, horrible disease will kill your database stone dead, but only after a long illness of poorly performing queries, frustrated users and suicidal developers! Is it advisable - in a word NO (picture me screaming!!!).