I'm working on a movie inventory database. This is a personal side project for me. I can use what I have, but I would like to make it the best design that I can while learning more about databases and designing databases.
Purpose:
This database is to help me track my movie collection and let me be able to find a movie I want to watch from it based on my criteria. Also to help keep track of lending out movies to friends and family and to keep track of my progress of watch a movie or show. Also to help keep track of what I'm planning to watch for a specific event such as a friend is coming over and we talked about watching all of these movies this coming saturday.
Diagrams
Tables
AudioLangs
+-----------+--------------+------------+---------------+
| Field | Type | Allow Null | Default Value |
+-----------+--------------+------------+---------------+
| Code | char(6) | No | | (PK)
| Audio | varchar(128) | No | |
| Subtitles | varchar(128) | No | |
+-----------+--------------+------------+---------------+
Categories
+----------+----------+----+--+
| Code | char(7) | No | | (PK)
| Category | char(10) | No | |
+----------+----------+----+--+
DiscEpisodes
+-------------+---------+----+---+
| MovieDiscID | int(12) | No | 0 | (PK) (FK1 -> MovieDiscs.ID)
| EpisodeID | int(12) | No | 0 | (PK) (FK2 -> Episodes.ID)
+-------------+---------+----+---+
Episodes
+-------------------+--------------+-----+---+
| ID | int(12) | No | | (PK)
| MovieCode | char(10) | No | | (FK1 -> Movies.Code)
| SeasonNum | int(12) | No | 1 | (FK2 -> Seasons.SeasonNum)
| EpisodeNum | int(12) | No | 1 |
| OverAllEpisodeNum | int(12) | Yes | 0 |
| Plot | varchar(128) | Yes | |
+-------------------+--------------+-----+---+
Might change that to seasons.moviecode for movie code.
Formats
+--------+----------+----+--+
| Code | char(3) | No | | (PK)
| Format | char(10) | No | |
+--------+----------+----+--+
Genres
+-------------+--------------+-----+--+
| Code | char(6) | No | | (PK)
| Genre | varchar(50) | No | |
| Description | varchar(128) | Yes | |
+-------------+--------------+-----+--+
MovieCrews
+------------+----------+-----+---+
| MovieCode | char(10) | No | | (PK) (FK1 -> Movies.Code)
| PersonID | int(11) | No | 1 | (PK) (FK2 -> Persons.ID)
| PositionID | char(6) | No | | (PK) (FK3 -> Positions.ID)
| SeasonNum | int(12) | Yes | | (FK4 -> Seasons.SeasonNum)
| EpisodeNum | int(12) | Yes | | (FK5 -> Episodes.id)
+------------+----------+-----+---+
MovieDiscs
+------------+--------------+-----+------+
| ID | int(12) | No | | (PK)
| MovieCode | char(10) | No | | (FK1 -> MovieFormats.MovieCode)
| FormatCode | char(3) | No | | (FK1 -> MovieFormats.FormatCode)
| SeasonNum | int(12) | Yes | | (FK2 -> Seasons.SeasonNum)
| DiscNum | int(12) | No | 1 |
| DiscTitle | varchar(128) | Yes | |
| Location | varchar(128) | Yes | |
| Status | varchar(128) | Yes | 'IN' |
+------------+--------------+-----+------+
MovieFormats
+------------+------------+-----+--+
| MovieCode | char(10) | No | | (PK) (FK1 -> Movies.Code)
| FormatCode | char(3) | No | | (PK) (FK2 -> Formats.Code)
| AudioLang | varchar(6) | Yes | |
+------------+------------+-----+--+
MovieGenres
+-----------+----------+----+--+
| MovieCode | char(10) | No | | (PK) (FK1 -> Movies.Code)
| GenreCode | char(6) | No | | (PK) (FK2 -> Genres.Code)
+-----------+----------+----+--+
MovieRelations
+--------------+----------+----+--+
| MovieCode | char(10) | No | | (PK) (FK1 -> Movies.Code)
| RelationCode | char(4) | No | | (PK) (FK2 -> Relations.Code)
| SecondMovie | char(10) | No | | (PK) (FK3 -> Movies.Code)
+--------------+----------+----+--+
MovieSeries
+-----------+-------------+----+--+
| Series | varchar(11) | No | | (PK)
| MovieCode | char(10) | No | | (PK) (FK1 -> Movies.Code)
+-----------+-------------+----+--+
Movies
+--------------+--------------+-----+---+
| Code | char(10) | No | | (PK)
| Title | varchar(128) | No | |
| Alt | varchar(128) | Yes | |
| Type | char(6) | No | | (FK1 -> Types.Code)
| Plot | varchar(128) | No | |
| Rated | char(6) | No | | (FK2 -> Ratings.Code)
| Score | float | Yes | 0 |
| Image | varchar(128) | Yes | |
| Category | char(7) | No | | (FK3 -> Categories.Code)
| Duration | time | Yes | |
| YearReleased | year(4) | Yes | |
| TotalEps | int(11) | No | 0 |
+--------------+--------------+-----+---+
Persons
+-----------+-------------+----+--+
| ID | int(11) | No | | (PK)
| FirstName | varchar(50) | No | |
| LastName | varchar(50) | No | |
+-----------+-------------+----+--+
Positions
+----------+--------------+----+--+
| Code | char(6) | No | | (PK)
| Position | varchar(128) | No | |
+----------+--------------+----+--+
Ratings
+-------+---------+----+---+
| Code | char(6) | No | | (PK)
| Value | int(1) | No | 0 |
+-------+---------+----+---+
Relations
+----------+----------+----+--+
| Code | char(4) | No | | (PK)
| Relation | char(20) | No | |
+----------+----------+----+--+
Rents
+-------------+-------------+-----+-----------------------+
| MovieDiscID | int(12) | No | 0 | (PK) (FK1 -> MovieDiscs.ID)
| UserId | varchar(50) | No | | (PK) (FK2 -> Users.Username)
| CheckOut | datetime | No | '0000-00-00 00:00:00' | (PK)
| CheckIn | datetime | Yes | |
| Status | varchar(4) | No | 'Out' |
+-------------+-------------+-----+-----------------------+
Seasons
+------------+----------+----+---+
| MovieCode | char(10) | No | | (PK) (FK1 -> MovieFormats.MovieCode)
| FormatCode | char(3) | No | | (PK) (FK1 -> MovieFormats.FormatCode)
| SeasonNum | int(12) | No | 1 | (PK)
+------------+----------+----+---+
Status
+--------+-------------+----+--+
| Code | char(6) | No | | (PK)
| Status | varchar(20) | No | |
+--------+-------------+----+--+
Types
+---------+--------------+-----+--+
| Code | char(6) | No | | (PK)
| Type | char(10) | No | |
| Meaning | varchar(128) | Yes | |
+---------+--------------+-----+--+
UserMovies
+-----------+-------------+-----+---+
| MovieCode | char(10) | No | | (PK) (FK1 -> Movies.Code)
| UserID | varchar(50) | No | | (PK) (FK2 -> Users.Username)
| Status | char(6) | No | | (FK3 -> Status.Code)
| CurrentEp | int(4) | No | 0 |
| TotalEps | int(11) | Yes | 0 |
+-----------+-------------+-----+---+
Users
+----------------+--------------+-----+-------+
| Username | varchar(50) | No | | (PK)
| Password | char(64) | No | |
| Salt | char(16) | No | |
| Email | varchar(128) | Yes | |
| RatingPassword | char(64) | Yes | |
| RatingPref | char(6) | Yes | | (FK1 -> Ratings.Code)
| FirstName | varchar(50) | No | |
| LastName | varchar(50) | No | |
+----------------+--------------+-----+-------+
WatchList
+-----------+--------------+----+--------------+
| UserID | varchar(50) | No | | (PK) (FK1 -> Users.Username)
| MovieCode | char(10) | No | | (PK) (FK2 -> Movies.Code)
| DateAdded | date | No | '0000-00-00' | (PK)
| Event | varchar(128) | No | | (PK)
+-----------+--------------+----+--------------+
Might just take out UserID on this one. Also might just merge into UserMovies, but I like having a table specifically for this purpose.
UPDATE:
Got the trigger to work for it and fix the foreign key in user movies referring to tool episodes.
Anything else you see that needs to be fixed or that could be done better?
Best Answer
I think that you really need an ER diagram here. Try YED for doing the diagram. It helps you visualise things better. I'm involved in designing rdbms for critical gov operations and was taught in university to never design things in code. I feel some of your tables have too many fks and are breaking the normalisation process.
If you have an ER diagram i would love to see it. Its the way I always start. First define entities, the attributes, then do relationship matrix, then the entity relationship diagram, then do out the tables and do 1nf etc. It is an awful lot of work to start off with but when you can visualise it it starts to make sense. it means less maintainability, more accuracy, more performance and better data.
Some rows have three pks - I presume that this is a combination pk or else it has broken the rules of normalisation - only one primary key per row. You will eventually run into difficulty. Would be interested in seeing more.
The diagram you have shown looks somewhat like an ER diagram but you do not appear to have considered the attributes or relationships in it e.g. one to many, many to many.
I would be really glad to help with the design. But normalisation has been broken here. Therefore your data will let you down in the long run. A well designed database is crucial here especially since you are tracking those brilliant movies :)
Any chance I could rent one from this db :)