(2013-05-17, 14:58)DanCooper Wrote: I have looked at your database.
Follow questions:
- I still do not understand why you link tables with primary keys want to use.
I use primary key only when a record can not be uniquely defined. And that's not necessary here.
Primary Key: set of unique values needed to identify univocally a single row
Every table must have a PK (if is not a trash kind folder), using the Unique Index is partial. In the case of link tables the PK is the couple if link ids.
Is more efficient and quicker.
(2013-05-17, 14:58)DanCooper Wrote: I think this is a better way to prevent duplicate entries occur:
Code:
--Tabelle: actorlinkmovie
--DROP TABLE actorlinkmovie;
CREATE TABLE actorlinkmovie (
idActor integer,
idMovie integer
);
CREATE UNIQUE INDEX ix_actorlinkmovie_1
ON actorlinkmovie
(idActor, idMovie);
CREATE UNIQUE INDEX ix_actorlinkmovie_2
ON actorlinkmovie
(idMovie, idActor);
na, is less efficient and leads to confusion as it makes people think that there could be duplicated rows due to the lack of a PK.
(2013-05-17, 14:58)DanCooper Wrote:
Why do you want no Writer and Director link tables?
Just when you usu primary key for idPerson and idMovie it is not possible eg Bruce Willis to save as an actor and director.
is not efficient. TMDB, IMDB structures are better, it is even much quicker to deserialize (in case we want to scrape all person info)
Writer, Music, Actors, etc... are all persons
So we need a Person table where store person info (an din the future we can add more info if we want) and then a second table where we distinguish what the person have done in each movie, and this is the link table.
Also from a code perspective is easier as we have to add/update only the person table.
(2013-05-17, 14:58)DanCooper Wrote:
What advantage do you see when the set is linked to a file instead of a movie?
Sorry maybe I did a mistake, the relationship are multiple:
Movie -> files (for all info, NFO, effective media, fanart, etc...)
Set -> movies (for the relationship in the set, which movies are part of the set)
Set -> files (for the set specific info like the fanart or an image)
The files table contains ALL files (subitle, movies, episodes, images, etc...) they are distinguished by the type flag. This is again much quicker and more flexible.
the link tables create the relationship between the assets and the files...
(2013-05-17, 14:58)DanCooper Wrote:
Why do you have removed all "Has*****" column? Do you want check every time "If NotNullOrEmpty -> PosterPath" while the movie is loading?
All main lists use this entries for the green tick mark.
I do not want to do that, but they are useless as fields as they have to be put in the View on the table, in other words those fields are
select count(file)>0 from files where type = 1 {fanart} and files.movieid = movieid
or even select count(file) from files where type = 1 {fanart} and files.movieid = movieid
in this way we avoid multiple updates, add file, update flag etc... is just a source of errors
In a nutshell we are using SQLite that is a relational DB... so let's take advantage of that to simplify the code
... and this is why I added all the PK, all FK and indexes. Once we insert, delete, update something we can be sure is coherent!
Also in the dev and debug phase is easier as the wrong insert/update/delete sequence will generate an immediate error.
M
Working on language files!
from 1780 string down to 600
)
