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