Database design
-
I'm a C# programmer and have very little experience of creating databases.
I usually get a database that an expert Database Admin has created and I do read and write
against it.
Now I need to understand how to design a database!
In my sample I have a CD database that will contain
Artist Name
Album Title
Song Title
Song Genre
Song Year
Song Length
My first try looks like this
Instinctively I feel like adding two tables that connect
Artist - Album
Album - Song
but I don't know if it's necessarily?
I have heard something about Foreign Keys...
So, if someone may suggest a design I would appreciate it!
Also if you can recommend a book on database design.
I'm using Microsoft SQL Express, if that matters.
Thank you in Advance
// Anders
-
Book on database design: look up Louis Davidson on Amazon (grab his SQL 2008 book)
I strongly suggest you get that and read it before you carry on. However...
The linking tables are a good idea. Without them, how do you represent a song that's on two albums (say a first release and a best of)? How do you represent an album that has multiple artists and artists that release multiple albums?
Foreign keys: http://www.blackwasp.co.uk/SQLForeignKeys.aspx and http://msdn.microsoft.com/en-us/library/ms175464.aspx
Some pointers on your design currently.
Nvarchar columns: Are you expecting artists, songs and albums named using non-latin alphabets? (japanese, thai, arabic, etc)?
The length column: Time is a point in time, not a timespan. 00:06:45 is 6 minutes, 45 seconds after midnight, it is not a duration of 6 minutes 45 seconds. Please don't fall into that trap. Store durations in minutes or seconds using smallint or int columns
Don't call columns ID. Confusing when you suddenly have 10+ columns named ID with no indication what they are the ID of. If you're going to use artificial primary keys, call them something recognisable. SongID, ArtistID, etc
You have artificial primary keys, but I don't see any alternate keys mentioned. That's a classic design pitfall that will hurt you later (you'll be able to put the same song in the table 5 times all with different IDs, they're actually duplicates, but they're not complete duplicate rows)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hello GilaMonster,
Thanks for your reply!
I will get the book by Louis Davidson, it will take some weeks to get it ...
I changed nvarchar to varchar. Don't really need unicode. I'm trusting that western character works, e.g. ÅÄÖ
I also changed lenght to int. I have to check what the smallint, tinyint has as limits...
I was under the impression that I would use <table>.<column> and therefore it would be a bit stupid to use Artist.artistId, but I have now changed it to artistId , albumId, songId.
BTW, what is the standard when naming tables and columns?
CamelCasing or pascalCasing?
About the alternate key. I have just read on WikiPedia their explanation of alternate key and they say that an alternate key is a unique key, that can be used to stop getting doublicates. I can't at the moment see how I could use this?
Maybe I need to add a table that has artistId + albumId + songId as a unique key?
// Anders
-
andis59 (1/7/2012)
I also changed lenght to int. I have to check what the smallint, tinyint has as limits...It's in Books Online, but smallint = 32767. Tinyint = 255
About the alternate key. I have just read on WikiPedia their explanation of alternate key and they say that an alternate key is a unique key, that can be used to stop getting doublicates. I can't at the moment see how I could use this?
How do you identify a song uniquely? (and I don't mean by the SongID column). How do you identify an artist uniquely? How do you identify an album uniquely?
Maybe I need to add a table that has artistId + albumId + songId as a unique key?
Err, NO! (what point would such a table add, seeing as those IDs are all unique by themselves?)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (1/7/2012)
It's in Books Online, but smallint = 32767. Tinyint = 255OK, then a smallint should be enough.
How do you identify a song uniquely? (and I don't mean by the SongID column). How do you identify an artist uniquely? How do you identify an album uniquely?
Well, a song would be Artist.name + Album.title + Song.title
album would be Artist.name + Album.title ( I might have to add a date here?)
about artist I might have to add some column to make it unique, since name could be shared between many artists...
Even this "simple" database is proven to be more complex than I thought!
Err, NO! (what point would such a table add, seeing as those IDs are all unique by themselves?)
Well, I realized that just minutes after I had replied...
Looks like I really need the book!
// Anders
-
andis59 (1/7/2012)
Well, a song would be Artist.name + Album.title + Song.titleWhich, since they're all in different tables can't be an alternate key.
album would be Artist.name + Album.title ( I might have to add a date here?)
Same here.
about artist I might have to add some column to make it unique, since name could be shared between many artists...
And as soon as you add a column to make it unique, it's no longer useful (because you already have such a column, its your primary key)
Even this "simple" database is proven to be more complex than I thought!
You can decide not to have alternate keys, but then you must decide that as part of the design process rather than not having them because you never considered the possibility. (if this is a personal project, I'd probably just leave them out)
Songs and albums, books and authors are often used in database design examples because they are deceptively difficult to do properly (and the definition of 'properly' can change depending on who's writing/teaching)
I once challenged a group of experienced database developers (who thought they knew design well) to design a database storing books, authors, publishers and genres. By the time they were done and we'd discussed their designs, they realised they didn't know design at all.
Err, NO! (what point would such a table add, seeing as those IDs are all unique by themselves?)
Well, I realized that just minutes after I had replied...
😀
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
CELKO (1/8/2012)
There is no such thing as a magical, generic, universal "id" to mimic a pre-RDBMS pointer chains.Actually, there is. See the movie as to why it should be considered ...
http://technet.microsoft.com/en-us/sqlserver/Video/gg508879
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I'm not an expert by any means, so take anything I say with a pinch of salt, but shouldn't "genre" be separated out into another table? Seems to me it's going to be restricted to a small set of specific values, so having it as a separate table seems more natural to me.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply