Communities

Writing
Writing
Codidact Meta
Codidact Meta
The Great Outdoors
The Great Outdoors
Photography & Video
Photography & Video
Scientific Speculation
Scientific Speculation
Cooking
Cooking
Electrical Engineering
Electrical Engineering
Judaism
Judaism
Languages & Linguistics
Languages & Linguistics
Software Development
Software Development
Mathematics
Mathematics
Christianity
Christianity
Code Golf
Code Golf
Music
Music
Physics
Physics
Linux Systems
Linux Systems
Power Users
Power Users
Tabletop RPGs
Tabletop RPGs
tag:snake search within a tag
answers:0 unanswered questions
user:xxxx search by author id
score:0.5 posts with 0.5+ score
"snake oil" exact phrase
votes:4 posts with 4+ votes
created:<1w created < 1 week ago
post_type:xxxx type of post
Search help
Notifications
Mark all as read See all your notifications »
Q&A

Create self-referencing many-to-many relation

+0
−4

I've turned the tables movies and scenes into media and now I don't know what to do with the movies_scenes relation.

CREATE TABLE IF NOT EXISTS "movies_scenes" (
    `movie_id` integer,
    `scene_id` integer,
    `scene_index` tinyint,
    foreign key(`movie_id`) references `movies`(`id`) on delete cascade,
    foreign key(`scene_id`) references `scenes`(`id`) on delete cascade,
    PRIMARY KEY(`movie_id`, `scene_id`)
);
Why does this post require moderator attention?
You might want to add some details to your flag.
Why should this post be closed?

1 comment thread

We need more context to be able to provide an answer (1 comment)

1 answer

+4
−0

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
)
  1. 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.

Why does this post require moderator attention?
You might want to add some details to your flag.

0 comment threads

Sign up to answer this question »