Create self-referencing many-to-many relation

I'm taking a significant leap by guessing what you are trying to do from your other questions. If you provide some additional info, this answer might become more or less relevent.

(I am also using a generalised SQL language here, so you may have to translate the exact concepts into your engine.)

If you have taken an existing table set of...

CREATE TABLE "movies" (
  "movie_id"     INTEGER PRIMARY KEY,
  "movie_name"   TEXT
)
CREATE TABLE "scenes" (
  "scene_id"     INTEGER PRIMARY KEY,
  "scene_file"   TEXT
)
CREATE TABLE "movies_scenes" (
  "movie_id"     INTEGER FOREIGN KEY REFERENCES "movies"."movie_id",
  "scene_id"     INTEGER FOREIGN KEY REFERENCES "scenes"."scene_id",
  "scene_index"  TINYINT,
  PRIMARY KEY ("movie_id", "scene_index" ASC)
)

...and generalised movies and scenes into one table...

CREATE TABLE "media" (
  "media_id"     INTEGER,
  "media_file"   TEXT
)

...then, if you need to model that a media item could be composed of other media items, you could introduce the concept of media having "sub-media". As I see it, there are two significant choices:

1. Decide that one media item could be composed of multiple child items (one-to-many), for which you could use a self-referencing tree:

ALTER TABLE "media" (
  ADD "child_media_id"   INTEGER NULL FOREIGN KEY REFERENCES "media"."media_id",
  ADD "sub_media_index"  TINYINT NULL,
  ALTER "media_file"     TEXT NULL
)

2. Decide that any media item could be composed of multiple child items, and that those child items could be part of any other media item (many-to-many), for which you could use a junction table:

CREATE TABLE "sub_media" (
  "parent_media_id"  INTEGER FOREIGN KEY REFERENCES "media"."media_id",
  "child_media_id"   INTEGER FOREIGN KEY REFERENCES "media"."media_id",
  "ordinal"          TINYINT,
  PRIMARY KEY("parent_media_id", "ordinal" ASC)
)

Depending on your database engine, and its support for hierarchical queries, this could get very messy.

Both options are relatively flexible, but you may find yourself having to resolve multiple levels of sub-media (a movie, comprised of multiple scenes, comprised of multiple shots, comprised of multiple lines).

Personally, unless you know you are going to need support for highly-generalised media, I would stick to the logical entities of movie and scene, as these are far easier to reason about, and you can reasonably enforce there not being multiple levels to traverse.

I might consider generalising the media aspect to:

CREATE TABLE "movies" (
  "movie_id"   INTEGER,
  "movie_name" TEXT
)
CREATE TABLE "scenes" (
  "scene_id"     INTEGER,
  "scene_file"   TEXT
)
CREATE TABLE "movies_scenes" (
  "movie_id"    INTEGER FOREIGN KEY REFERENCES "movies"."movie_id",
  "scene_id"    INTEGER FOREIGN KEY REFERENCES "scenes"."scene_id",
  "scene_index" TINYINT,
  PRIMARY KEY ("movie_id", "scene_index")
)
CREATE TABLE "media" (
  "media_id"    INTEGER PRIMARY KEY
  "media_file"  TEXT NOT NULL UNIQUE
)
ALTER TABLE "movies" (
  ADD "media_id"  INTEGER NULL FOREIGN KEY REFERENCES "media"."media_id"
)
ALTER TABLE "scenes" (
  ADD "media_id"  INTEGER NULL FOREIGN KEY REFERENCES "media"."media_id"
)

...which would give me a means to say that my movie consists of one file (movies.media_id IS NOT NULL), or that my movie consists of multiple scenes (movies.media_id IS NULL AND scenes.media_id IS NOT NULL).

By keeping the logical entities as first-order objects, rather than generalising, it is easier to understand (at least, in my opinion).

You can, for example, also easily reason about adding an actors entity (if that were your desire):

CREATE TABLE "actors" (
  "actor_id"    INTEGER PRIMARY KEY,
  "actor_name"  TEXT
)
CREATE TABLE "movie_actors" (
  "movie_id"    INTEGER FOREIGN KEY REFERENCES "movies"."movie_id",
  "actor_id"    INTEGER FOREIGN KEY REFERENCES "actors"."actor_id"
  PRIMARY KEY ("movie_id", "actor_id")
)
CREATE TABLE "scene_actors" (
  "scene_id"    INTEGER FOREIGN KEY REFERENCES "scenes"."scene_id",
  "actor_id"    INTEGER FOREIGN KEY REFERENCES "actors"."actor_id"
  PRIMARY KEY ("scene_id", "actor_id")
)

...and easily be able to see all the short scenes with your favourite actors in them, or all the movies by actor.

While the same is possible with a generalised media table, by keeping the first-order entites, you can easily determine which of your media is a movie and which is a scene without having to resort to some type of media_tag property.

posted over 1 year ago

CC0

1y ago

jimbobmcgee‭

41 reputation 0 1 4 1