3

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

Diagram 1

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?

3

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 :)

  • Sorry I have just looked at the ER diagram. I need to look at it a bit more I'll normalise the data myself and send it on to see if you agree. there is one thing that I noticed. There seems to be a table for relationships (I've never seen this) - this sticks out to me as not sitting well. I'll build the tables from that in excel tomorrow and attach up to 3NF. I have some time to kill - snowed in and bored :) I'll take more time to look at the tables tomorrow. – Treasa Jan 13 '15 at 23:39
  • I would appreciate it. I think I know what table your talking about, that table goes with the movie relationships. So in it; it has a code and then what the code stands for such as pre for prequel. For example Matrix (The Matrix) and Matrix2 (Matrix movie 2) Matrix pre Matrix2 (Matrix is a prequel to Matrix movie 2). Probably could find a better name for that table, but I figured I would make a table for that, because is also has some uncommon one such as Alternative setting which is the same story, but different time period or place. – user2537388 Jan 14 '15 at 21:19
  • Apologies. It's taking time for me to consider the entities and their attributes. I am doing 1NF at the moment and I notice two tables for movie disks, meaning some duplication that I would be concerned about in terms of data consistency and duplication. Also status has been used twice as an attribute and once as an entity. This is only necessary once in a rents relationship. Furthermore, I think rents should be defined as a terenary relationship between the movie and the user, meaning that it will require its own table. I would not have defined it as an entity on its own? – Treasa Jan 14 '15 at 22:14
  • It's okay, I appreciate the help. Are you talking about movie discs and disc episodes? The movie discs is to help know how many discs there are for a particular series in a particular format. I know I could simplfy that, but I have part of a series on blu-ray and the other part on dvd so I made it a little more complicate for that reason. As for the episode part. I wanted to which episodes were on each disc without having to do the math of oh 5 were on disc 1 so 5 on disc 2 are these episodes. I only see one status in rent? Also rent is its own table (which is between user and movies)? – user2537388 Jan 15 '15 at 16:04
  • Just put in the format as an attribute. You wouldn't have a mixture of blu ray or dvd for a season. The ER diagram I am looking at has status as an attribute in the moviedisks entity and the rents entity. Status is also its own entity and has a 1:M relationship with usermovies. If you simplify the movie disks great. You could have a default of zero and just call it season 1, 2 etc. These should all depend on one primary key only. I'll keep working at the tables. – Treasa Jan 15 '15 at 21:36
1

Looking at the design that you have added I would like to advice you that most of table don't have any Primary key.

Primary key for most of the table with Int Data Type, will help in relating entry with other table indexes and future change can be easily accommodated.

UserID varchar(50) - If these UserId are just for Internal purpose, then better change that to int.

Most of table have column caled Code, Will be fixed? or it char type if it is some unique value for each row, make it an auto-incremented column and mark as primary key.

On normalization part, it is still difficult to say anything as the requirement is a concern.

You can have a look to one of my Movie Rental Database Design, Although the table names are with some old pattern and designed for SQL Server but the concept of normalization will work for you

  • Actually all the tables have a primary key it just doesn't show with the diagram or table structure for what ever reason. As for UserID it's just the shorthand for username and points to the username field in users. As for code most are char to make it easier to know what I'm entering and reading when I look at data. For example code for format DVD means DVD, BLU means Blu-ray, HDD means hard drive, ITU means iTunes. I know I could just use a serial id, but then I would have to look up the value and so forth and this just makes it easier. – user2537388 Sep 14 '14 at 15:25
  • that good that all tables have primary key, and If userId is same as username then you can keep it consistence or add with Fk as prefix, otherwise the design looks fine to me. – vijayp Sep 14 '14 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.