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?