On the topic of the original database abstraction proposal (standardization of names, etc.), it would be great if the database facilities for foreign keys were used. SQLite
(requires enabling a pragma), MySQL
, and PostgreSQL
all have foreign key support. It would help ensure data integrity and make the schema more intelligible. More primary keys and "not null" columns (possibly with defaults) might be useful too (does a movie with no title make sense?), although frequently unique indexes seem to take on part of the role of primary keys.
I'll also second renaming the 'cxx' columns to something more descriptive (although fortunately SQLAlchemy lets me assign them a key that I can use to refer to them).
Columns containing XML (thumbs, fan art) should be broken out into another table, since they're really an associated list, not flat data; since it would be one to many, no link tables would be necessary.
Given a general understanding of foreign keys
, I wouldn't expect I would need to explain where foreign keys would be useful, but just in case, as an example, the actorlinkmovie table's idActor should be a foreign key to actors.idActor, and idMovie a foreign key to movie.idMovie, with an 'on delete cascade' on idMovie (if the movie is deleted, so are the referring actorlinkmovie row(s)) and 'on delete restrict' on idActor (deleting a linked actor produces a constraint violation).
Unless there's a good reason why this is infeasible - or all these things are already being done quietly by firnsy or others - I will attempt to add foreign keys in my database (using SQLite), and, all going well, make a patch to create them in the table creation/update code and add it to a Trac ticket. (And perhaps implement some of the other ideas above too.)
As to why it came up: I'm writing some Python code to add movie details directly to the database, so I'm getting quite up close and personal with the tables. I'll eventually submit the code for inclusion in the tools folder, in case the modules can prove useful to others. (Naturally, I keep database backups in case I muck something up, and was initially working with a copy of the database.)