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
Community Proposals
Community Proposals
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

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.

Post History

75%
+4 −0
Q&A 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...

posted 1y ago by jimbobmcgee‭  ·  edited 1y ago by jimbobmcgee‭

Answer
#2: Post edited by user avatar jimbobmcgee‭ · 2022-12-22T19:20:43Z (over 1 year ago)
  • 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.
  • 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.
  • 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.
#1: Initial revision by user avatar jimbobmcgee‭ · 2022-12-22T19:19:55Z (over 1 year ago)
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.

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.