SQLite Forum

Is there any other solution or a naming recommendation for this situation?
Login

Is there any other solution or a naming recommendation for this situation?

(1.1) By Yuzem (yuzem1993) on 2021-01-15 13:33:58 edited from 1.0 [link] [source]

Hi everyone!

I have the following tables:

movie(id, title)

tv(id, title)

list(id, name)

movie_list(movie_id, list_id)

tv_list(tv_id, list_id)

The Problem

I can't create empty lists only for movies/tv_shows. If I show empty lists, I have to show them for movies and for tv_shows.

Solution

Use the following tables: movie(id, title)

tv(id, title)

list_movie(id, name)

list_tv(id, name)

movie_list(movie_id, list_id)

tv_list(tv_id, list_id)

Naming recommendation

The names get very confusing, even more if I add more tables like books or comics. Is there any other solution or a naming recommendation for this situation?

Thanks in advance!

(2) By Simon Slavin (slavin) on 2021-01-16 08:26:18 in reply to 1.1 [link] [source]

Having a table called "list" (or a table called "table") is a bad sign.

Actually I can't figure out what you're using all these tables for. I assume movie(id, title) is all the movies your program has to know about. From that, I assume tv(id, title) is all the programs shown on TV your program has to know about. What are the other tables for ?

(3) By Yuzem (yuzem1993) on 2021-01-16 09:43:09 in reply to 2 [source]

"list" holds different lists. For example: Whachlist, Favorites, Recommended by Tom, Vampires, etc... "movie_list" and "tv_list" will indicate which movie is in which list.

The problem with this is that if I create an empty list, I have to display it in movies and tv shows. For example, lets say I create the empty list "Vampires" only for movies, I have no way to know that this list is only for movies. Thats way I replace the table "list" by two tables: "list_tv" and "list_movie". That way I can create the list "Vampires" in "list_movie" and I know that this list is only for movies.

(4) By Keith Medcalf (kmedcalf) on 2021-01-16 10:58:56 in reply to 3 [link] [source]

So you only have two things: Watchables and Lists. Whether the Watchable or List is a Watchable of type Movie or type TV is an attribute of both Watchables and Lists. Then you have a table that list what Watchables are in what Lists, and a trigger to prevent you from putting the wrong type of thing in the wrong type of list.

create table Watchables
(
   id integer primary key,
   movie integer not null check (movie is (0,1)),
   title text not null collate nocase,
   unique (title, movie)
);

create table Lists
(
   id integer primary key,
   movie integer not null check (movie in (0, 1)),
   name text not null collate nocase,
   unique (name, movie)
);
create table ListMembers
(
   Lists_id integer not null references Lists,
   Watchables_id integer not null references Watchables,
   primary key (Lists_id, Watchables_id),
   unique (Watchables_id, Lists_id)
) without rowid;
create trigger before insert on ListMembers
begin
  select raise(ABORT, 'Watchables type does not match Lists type')
   where (
          select movie
            from Watchables
           where id == new.Watchables_id
         ) <>
         ( select movie
             from Lists
            where id == new.Lists_id
         );
end;

(5) By Yuzem (yuzem1993) on 2021-01-16 12:04:36 in reply to 4 [link] [source]

I need different tables for movies and tv shows because they have different columns. To make it simpler for the example I reduced the number of columns. Also, I may need to add other tables like books and comics for which I want to create lists.

I didn't mention that I would prefer to only use foreign keys and avoid triggers.

These are the tables I have without the irrelevant columns:

CREATE TABLE movie (
    id                INTEGER PRIMARY KEY,
    title           TEXT,
);

CREATE TABLE tv (
    id                INTEGER PRIMARY KEY,
    title           TEXT,
);

CREATE TABLE movie_list (
    movie_id INTEGER NOT NULL ON CONFLICT IGNORE
        REFERENCES movie(id)
            ON DELETE CASCADE
            ON UPDATE CASCADE DEFERRABLE,
    list_id INTEGER NOT NULL ON CONFLICT IGNORE
        REFERENCES list_movie(id)
            ON DELETE CASCADE
            ON UPDATE CASCADE DEFERRABLE,
    UNIQUE(movie_id, list_id) ON CONFLICT IGNORE
);

CREATE TABLE tv_list (
    tv_id    INTEGER NOT NULL ON CONFLICT IGNORE
        REFERENCES tv(id)
            ON DELETE CASCADE
            ON UPDATE CASCADE DEFERRABLE,
    list_id    INTEGER NOT NULL ON CONFLICT IGNORE
        REFERENCES list_tv(id)
            ON DELETE CASCADE
            ON UPDATE CASCADE DEFERRABLE,
    UNIQUE(tv_id, list_id) ON CONFLICT IGNORE
);

CREATE TABLE list_movie (
    id        INTEGER PRIMARY KEY,
    name    TEXT UNIQUE ON CONFLICT IGNORE
               NOT NULL ON CONFLICT IGNORE,
);

CREATE TABLE list_tv (
    id        INTEGER PRIMARY KEY,
    name    TEXT UNIQUE ON CONFLICT IGNORE
               NOT NULL ON CONFLICT IGNORE,
);

(6) By Simon Slavin (slavin) on 2021-01-16 13:25:05 in reply to 3 [link] [source]

Are all lists just for one type of medium ? If so, add another column to list saying 'tv' or 'movie', etc..

(7) By Yuzem (yuzem1993) on 2021-01-16 14:32:36 in reply to 6 [link] [source]

The lists are for movies, tv shows, persons, and maybe in the future, for comics and books also.
The problem with adding another column for media type is that I don't know how to use foreign keys in that case.
REFERENCES list(id) will not work, if I use UNIQUE(id, mediatype) in list table.
I believe that multiple columns references are not posible.
I would need something like:

CREATE TABLE movie_list (
    movie_id
        REFERENCES movie(id)
            ON DELETE CASCADE
            ON UPDATE CASCADE DEFERRABLE,
    list_id
        REFERENCES list('movie', id)   <=== 'movie' for mediatype doesn't work
            ON DELETE CASCADE
            ON UPDATE CASCADE DEFERRABLE,
    UNIQUE(movie_id, list_id) ON CONFLICT IGNORE
);

I know that I can use triggers but I prefer to use only foreign keys.

Thank you all so far, any other ideas?

(8) By Yuzem (yuzem1993) on 2021-01-16 14:39:21 in reply to 6 [link] [source]

Wait a minute! Maybe... if I use UNIQUE(mediatype, name) instead of UNIQUE(id, mediatype)... I think this should work.