Database Design Primay Key, ID vs String

Question

Hi, I am currently planning to develop a music streaming application. And i am wondering what would be better as a primary key in my tables on the server. An ID int or a Unique String.

Methods 1:

Songs Table: SongID(int), Title(string), Artist*(string), Length(int), Album*(string)

Genre Table Genre(string), Name(string)

SongGenre: SongID*(int), Genre*(string)

Method 2

Songs Table: SongID(int), Title(string), ArtistID*(int), Length(int), AlbumID*(int)

Genre Table GenreID(int), Name(string)

SongGenre: SongID*(int), GenreID*(int)

Key: Bold = Primary Key, Field* = Foreign Key

I'm currently designing using method 2 as I believe it will speed up lookup performance and use less space as an int takes a lot less space then a string.

Is there any reason this isn't a good idea? Is there anything I should be aware of?

Answer 1

You are doing the right thing - identity field should be numeric and not string based, both for space saving and for performance reasons (matching keys on strings is slower than matching on integers).

Answer 2

MSSQL can generate these id's for you when using an int (see IDENTITY keyword)

Answer 3

My recommendation is: use ids.

You'll be able to rename that "Genre" with 20000 songs without breaking anything.

The idea behind this is that the id identifies the row in the table. Whatever the row has is something that doesn't matters in this problem.

Answer 4

This is in large part a matter of personal preference.

My personal opinion and practice is to always use integer keys and to always use surrogate rather than natural keys (so never use anything like social security number or the genre name directly).

There are cases where an auto number field is not appropriate or does not scale. In these cases it can make sense to use a GUID, which can be a string in databases that do not have a native datatype for it.