>John,
>
>>The other thing that jumped out at me is there is no integer primary key. Primary keys should be meaningless numbers not fields tied to courses or whatever..
>
>Who says a meaningless (surrogate) key has to be an integer? Integer is probably most often used, but guids are nice, too. And there is nothing to keep you from using a character-based field for a surrogate key, although that may not be the case here.


I believe what I said. Here's why:

1. Meaningful keys present a few challenges that you'll want to be aware of. First, they tend to change over time (because, after all, they are storing data that means something). If a meaningful PK is used as a foreign key in related child tables, then you'll need to update the FK values in the child tables as well to maintain referential integrity. Not a huge deal, but it means extra work for you.

2. Meaningful keys can pose performance problems if they're quite large. Consider a PK that is someone's social security number. If you take out the dashes, a SSN is 9 characters. If you pack that into a char(9), it will use 9 bytes of storage in the base table and in any tables where it is used as a FK. Contrast that with an autoincremented integer, which uses 4 bytes of storage. In this case, the difference is fairly minor, but you'll incur more I/O and your indexes on this field will be larger, which can slow access through those indexes.

3. In my studies, we were marked down for using multiple field keys as primary keys, using non-surrogate keys as primary keys, etc. I believe it is a bad practice to do otherwise.