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.

  • Why do you think you need "to replace the {Studio,Licensor,...}Entry classes (and tables) with one "MetadataEntry" class"? What are you planning to achieve by this?
    – mustaccio
    Nov 3, 2017 at 15:38
  • ARRRGGHHHH! I feel a bout of 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!!!).
    – Vérace
    Nov 3, 2017 at 16:56
  • @Vérace can you elaborate? I'll admit I'm not very good with databases. I'm just implementing this part because my partner is busy at the moment and when I discussed this with him he insisted having one table for the metadata would be better. Do you think the method I was going to originally do it, having many ...Entry tables, is better?
    – Kahr Kunne
    Nov 3, 2017 at 19:31
  • Basically, different objects belong in different in tables. Sounds like you're going down the EAV route - this anti-pattern appeals to database beginners because they see it as a "simplification". The only thing it simplifies is the making of mistakes. Modern servers can cope with thousands of tables! Google EAV and Joe Celko (also try the words MUCK and OTLT (or check out my answer here!
    – Vérace
    Nov 3, 2017 at 19:59
  • Alright, what we were trying to do does indeed seem to be this antipattern. I'll implement our system in the "many tables" fashion, then. Thanks for your advice!
    – Kahr Kunne
    Nov 3, 2017 at 21:09


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.