Correct data types for my database

Question

I have to make Movies database with the following tables:

• Directors (Id, DirectorName, Notes)

• Genres (Id, GenreName, Notes)

• Categories (Id, CategoryName, Notes)

• Movies (Id, Title, DirectorId, CopyrightYear, Length, GenreId, CategoryId, Rating, Notes)

Let me give you my query for Directors database:

CREATE TABLE Directors (
Id INT PRIMARY KEY IDENTITY,
DirectorName NVARCHAR(60) NOT NULL,
Notes NVARCHAR(MAX) 
)

Genres and Categories tables are almost the same. I have only changed the size of NVARCHAR.

I am having doubts about Movies table. I don't have experience with databases and I am not sure if I chose the right data types for each column. I would be very grateful if someone with more experience can help me and see what I've done.

CREATE TABLE Movies (
Id INT PRIMARY KEY IDENTITY,
Title NVARCHAR(60) NOT NULL,
DirectorId INT FOREIGN KEY REFERENCES Directors(Id),
CopyrightYear SMALLINT NOT NULL,
[Length] FLOAT(1) NOT NULL,
GenreId INT FOREIGN KEY REFERENCES Genres(Id),
CategoryId INT FOREIGN KEY REFERENCES Categories(Id),
Rating FLOAT(1) NOT NULL,
Notes NVARCHAR(MAX) 
)

Answer 1

The biggest issues that jump out to me are the lengths of the NVARCHAR columns (60 characters will certainly not be long enough for all movie titles, and probably not enough for all director names), and the use of the FLOAT data type. In my opinion and experience, FLOAT is almost never the right data type to use.

For Length, the correct type would depend on what is being stored. Most of the time, movie length is stored as a number of minutes (rounded to the nearest whole number). If that is how you are storing it, I would suggest SMALLINT. However, if you are storing length as hours or something where you need non-integer values, I would suggest the NUMERIC (or DECIMAL; they are synonyms and mean exactly the same thing) data type (choose precision and scale to meet your needs; see https://learn.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql?view=sql-server-2017 for details).

For Rating, I'm guessing you're not talking about something like MPAA rating (which is a string value), but some kind of critics rating or something like that. Again, I would suggest using NUMERIC instead of FLOAT.

Since your question was only about data types, that is all that I have addressed. However, there are certainly other design concerns I would have with this design (like, how are you handling movies with multiple directors, genres, and categories?). But that seems to be out of scope for this specific question.

Answer 2

Your data types look fine. You probably only need SMALLINT for GenreID, but that's a quibble. In that spirit:

• I'd change Length to LengthMin or LengthMinutes to make it clear you're not using hours or something (and avoid using a reserved word).
• As Jacob H. pointed out, 60 characters isn't enough for all movies.
• Most directors have two names. Some have several. It's complicated, but you should at least add LastName, for sorting.
• Some movies have more than one director. You could store the combinations, so "John Doe and Alan Smithy" would be a single record associated with one or more movies, but it would be more robust to allow a movie to link to multiple Director records.
• Putting a Notes field in every table is probably unnecessary. Free text is sometimes a necessary evil, but should be a last resort, not a habit.
• What does Rating represent? If it's either critical or audience ratings, you'll need more than one per movie. Even if the whole point of your database is to aggregate data into one master rating to rule them all, you'll need a table with the input data.
• If Rating is the release rating (G, PG, R, etc.), that should be stored in a ReleaseRatings table, probably with the PK on { MovieID, CountryID }. If you're certain you'll only be interested in one country, it can be a simple lookup table like Genres.
• How about an Actors table? That's another many-to-many relationship, so you'd also need a Roles or Performances table. Oh, and some actors have multiple names; not just first and last, but professional and legal, or married and maiden, etc. Welcome to the rabbit hole!