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.