Sql-server – Correct data types for the database

sql servertable

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

Best Answer

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://docs.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.

Related Question