Perdón si esto ha sido respondido en otro lado. He estado buscando SO y no he podido traducir las preguntas y respuestas aparentemente relevantes a mi escenario.

Estoy trabajando en un divertido proyecto personal en el que tengo 4 esquemas principales (salvo relaciones por ahora):

• Persona (nombre, biografía)
• Episodio (título, trama)
• Clip (url, marca de tiempo)
• URL de la imagen)

Restricciones (Base de las relaciones):

1. Una Persona puede aparecer en varios episodios, así como en varios clips e imágenes de esos episodios (pero es posible que no esté en todos los clips/imágenes relacionados con un episodio).
2. Un episodio puede contener varias personas, clips e imágenes.
3. Una imagen/clip solo se puede relacionar con un solo episodio, pero se puede relacionar con varias personas.
4. Si una Persona ya está asignada a un episodio, entonces cualquier clip o imagen asignada a la Persona solo puede ser de uno de esos episodios o (si es nuevo) solo debe ser capaz de tener uno de los episodios en los que apareció la Persona asociada. al clip/imagen.
5. Si un episodio ya tiene una persona asignada, entonces cualquier clip o imagen asignada al episodio debe estar relacionado con al menos una de esas personas o (si es nuevo) solo debe ser capaz de tener una o más de las personas del episodio asociado. al clip/imagen.

He diseñado la estructura de la base de datos así:

Esto genera el siguiente sql:

 DROP TABLE IF EXISTS episodes; DROP TABLE IF EXISTS personas; DROP TABLE IF EXISTS personas_episodes; DROP TABLE IF EXISTS clips; DROP TABLE IF EXISTS personas_clips; DROP TABLE IF EXISTS images; DROP TABLE IF EXISTS personas_images; CREATE TABLE episodes ( id INT NOT NULL PRIMARY KEY, title VARCHAR(120) NOT NULL UNIQUE, plot TEXT, tmdb_id VARCHAR(10) NOT NULL, tvdb_id VARCHAR(10) NOT NULL, imdb_id VARCHAR(10) NOT NULL); CREATE TABLE personas ( id INT NOT NULL PRIMARY KEY, name VARCHAR(30) NOT NULL, bio TEXT NOT NULL); CREATE TABLE personas_episodes ( persona_id INT NOT NULL, episode_id INT NOT NULL, PRIMARY KEY (persona_id,episode_id), FOREIGN KEY(persona_id) REFERENCES personas(id), FOREIGN KEY(episode_id) REFERENCES episodes(id)); CREATE TABLE clips ( id INT NOT NULL PRIMARY KEY, title VARCHAR(100) NOT NULL, timestamp VARCHAR(7) NOT NULL, link VARCHAR(100) NOT NULL, episode_id INT NOT NULL, FOREIGN KEY(episode_id) REFERENCES episodes(id)); CREATE TABLE personas_clips ( clip_id INT NOT NULL, persona_id INT NOT NULL, PRIMARY KEY (clip_id,persona_id), FOREIGN KEY(clip_id) REFERENCES clips(id), FOREIGN KEY(persona_id) REFERENCES personas(id)); CREATE TABLE images ( id INT NOT NULL PRIMARY KEY, link VARCHAR(120) NOT NULL UNIQUE, path VARCHAR(120) NOT NULL UNIQUE, episode_id INT NOT NULL, FOREIGN KEY(episode_id) REFERENCES episodes(id)); CREATE TABLE personas_images ( persona_id INT NOT NULL, image_id INT NOT NULL, PRIMARY KEY (persona_id,image_id), FOREIGN KEY(persona_id) REFERENCES personas(id), FOREIGN KEY(image_id) REFERENCES images(id));

Y he intentado crear el mismo esquema en los modelos SQLAchemy (teniendo en cuenta SQLite para pruebas, PostgreSQL para producción) así:

 # db is a configured Flask-SQLAlchemy instance from app import db # Alias common SQLAlchemy names Column = db.Column relationship = db.relationship class PkModel(Model): """Base model class that adds a 'primary key' column named ``id``.""" __abstract__ = True id = Column(db.Integer, primary_key=True) def reference_col( tablename, nullable=False, pk_name="id", foreign_key_kwargs=None, column_kwargs=None ): """Column that adds primary key foreign key reference. Usage: :: category_id = reference_col('category') category = relationship('Category', backref='categories') """ foreign_key_kwargs = foreign_key_kwargs or {} column_kwargs = column_kwargs or {} return Column( db.ForeignKey(f"{tablename}.{pk_name}", **foreign_key_kwargs), nullable=nullable, **column_kwargs, ) personas_episodes = db.Table( "personas_episodes", db.Column("persona_id", db.ForeignKey("personas.id"), primary_key=True), db.Column("episode_id", db.ForeignKey("episodes.id"), primary_key=True), ) personas_clips = db.Table( "personas_clips", db.Column("persona_id", db.ForeignKey("personas.id"), primary_key=True), db.Column("clip_id", db.ForeignKey("clips.id"), primary_key=True), ) personas_images = db.Table( "personas_images", db.Column("persona_id", db.ForeignKey("personas.id"), primary_key=True), db.Column("image_id", db.ForeignKey("images.id"), primary_key=True), ) class Persona(PkModel): """One of Roger's personas.""" __tablename__ = "personas" name = Column(db.String(80), unique=True, nullable=False) bio = Column(db.Text) # relationships episodes = relationship("Episode", secondary=personas_episodes, back_populates="personas") clips = relationship("Clip", secondary=personas_clips, back_populates="personas") images = relationship("Image", secondary=personas_images, back_populates="personas") def __repr__(self): """Represent instance as a unique string.""" return f"<Persona({self.name!r})>" class Image(PkModel): """An image of one of Roger's personas from an episode of American Dad.""" __tablename__ = "images" link = Column(db.String(120), unique=True) path = Column(db.String(120), unique=True) episode_id = reference_col("episodes") # relationships personas = relationship("Persona", secondary=personas_images, back_populates="images") class Episode(PkModel): """An episode of American Dad.""" # FIXME: We can add Clips and Images linked to Personas that are not assigned to this episode __tablename__ = "episodes" title = Column(db.String(120), unique=True, nullable=False) plot = Column(db.Text) tmdb_id = Column(db.String(10)) tvdb_id = Column(db.String(10)) imdb_id = Column(db.String(10)) # relationships personas = relationship("Persona", secondary=personas_episodes, back_populates="episodes") images = relationship("Image", backref="episode") clips = relationship("Clip", backref="episode") def __repr__(self): """Represent instance as a unique string.""" return f"<Episode({self.title!r})>" class Clip(PkModel): """A clip from an episode of American Dad that contains one or more of Roger's personas.""" __tablename__ = "clips" title = Column(db.String(80), unique=True, nullable=False) timestamp = Column(db.String(7), nullable=True) # 00M:00S link = Column(db.String(7), nullable=True) episode_id = reference_col("episodes") # relationships personas = relationship("Persona", secondary=personas_clips, back_populates="clips")

Sin embargo, observe el comentario de FIXME . Tengo problemas para descubrir cómo restringir las relaciones de muchos a muchos en personas+imágenes, personas+clips y personas+episodios de manera que todos se miren entre sí antes de agregar una nueva entrada para restringir las posibles adiciones. al subconjunto de elementos que cumplen los criterios de esas otras relaciones de muchos a muchos.

¿Puede alguien proporcionar una solución para garantizar que las relaciones de muchos a muchos respeten la relación de episode_id en las tablas principales?

Edite para agregar un ejemplo de pseudomodelo del comportamiento esperado

 # omitting some detail fields for brevity e1 = Episode(title="Some Episode") e2 = Episode(title="Another Episode") p1 = Persona(name="Raider Dave", episodes=[e1]) p2 = Persona(name="Ricky Spanish", episodes=[e2]) c1 = Clip(title="A clip", episode=e1, personas=[p2]) # should fail i1 = Image(title="An image", episode=e2, personas=[p1]) # should fail c2 = Clip(title="Another clip", episode=e1, personas=[p1]) # should succeed i2 = Image(title="Another image", episode=e2, personas=[p2]) # should succeed