1

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) 
)
  • What happens when you try to enter the movie title Don't Be a Menace to South Central While Drinking Your Juice in the Hood? – Jacob H Aug 20 '19 at 21:25
  • What are "categories?" Almost anything you can apply to a movie (silent, black and white, foreign language, Oscar-winning, rating, critical score) is a many-to-many relationship. – Jon of All Trades Aug 20 '19 at 21:25
  • @JacobH, what size do you suggest? – Andrew Rogers Aug 20 '19 at 21:39
  • @JonofAllTrades, I just want to make sure the types I used are the right ones. For example, I wasn't sure about FLOAT(1). – Andrew Rogers Aug 20 '19 at 21:41
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://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.

| improve this answer | |
  • Thank you! I am not sure I understood why FLOAT is almost never the right data type to use. Can you give me some examples? – Andrew Rogers Aug 20 '19 at 21:47
  • 1
    @AndrewRogers From the documentation: "Floating point data is approximate; therefore, not all values in the data type range can be represented exactly." It is approximate and imprecise, and can give unexpected results when casting to other numeric types. The only time it provides an advantage over precise types (like INT or NUMERIC) is when you need to represent VERY large numbers and precision isn't important. – Scott M Aug 20 '19 at 21:59
  • Thank you! I do not have enough reputation to upvote your comment. I am sorry. Jon answered first, and I've already accepted his answer. – Andrew Rogers Aug 21 '19 at 19:20
  • @AndrewRogers Not that it really matters, but technically my answer was posted a few seconds before Jon's. Also, while his suggestions are good ones as far as database design, most of his answer isn't really related to data types, which is what your question was about. I was really trying to keep my answer focused on your specific question. – Scott M Aug 21 '19 at 20:51
  • I didn't know that I can change the accepted answer. It's done right now. :) – Andrew Rogers Aug 21 '19 at 22:21
0

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!
| improve this answer | |
  • Thank you! Are 200 characters enough? – Andrew Rogers Aug 20 '19 at 21:47
  • 1
    It should be. That's enough for Night of the Day of the Dawn of the Son of the Bride of the Return of the Revenge of the Terror of the Attack of the Evil, Mutant, Hellbound, Flesh-Eating Subhumanoid Zombified Living Dead, Part 3. I hear the book was better. – Jon of All Trades Aug 20 '19 at 22:25
  • Thank you! I accepted your answer. :) – Andrew Rogers Aug 21 '19 at 19:23

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.