Post History

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.