Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Create self-referencing many-to-many relation
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`)
);
1 answer
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:
- 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
)
- 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.
1 comment thread