0

I've been revisiting why I've been doing certain things in SQL Server 2005 data modeling.

I've habitually used identities for table keys, making unique indexes for the actual data that should be unique instead of keying off that.

So, if I had a student table, I might have:

ID identity primary key,
StudentIdentifier char(8)

I'd put a unique index on StudentIdentifier since this is the code that the university would use to reference a student, but I'd store Student.ID in any tables that referenced Student.

Is using an autoNumber Identity field still a good practice in SQL Server (version 2005)?

Also -

I use int codes for lookup values and store those ints in the corresponding referencing tables.

For example, I might have a workflow table like this:

id  statuscode      DESCRIPTION         
1   CIProgress      CI Grade in Progress
2   Finalized       Finalized           
3   NeedsSingle     Needs Single Read   
4   NeedsFirst      Needs First Read    
5   SingleProgress  Single Read in Progress

In a table that had a workflow attribute, I'd store the id (1) and then join against the workflow table to get the statuscode or description.

Is it okay to store the statuscode, for example, instead? How much efficiency am I gaining by using the int over a short code?

I've seen a lot of implementations in the 15 years since I started coding, and it occurs to me that maybe i'm taking old "rules" to heart without thinking about practice.

Caveatrob
  • 12,667
  • 32
  • 107
  • 187
  • Are you really going to stir the surrogate vs natural key pot? Personally I don't use them if there is a natural key as it's easier to see what's unique but there is no "one" way and it's an even toss up as to which one is "better". – Ben Sep 06 '12 at 20:31
  • ANd I almost always use surrogate keys, as the first column, so I can do "select * from order by 1 desc" and see the most recent additions.
     – Gordon Linoff Sep 06 '12 at 20:39
  • I wasn't trying to stir a pot - I remember those arguments. Just wondering what people might use in particular circumstances. For example, would you have some tables keyed off surrogate and some off natural? I'm thinking of natural keying the lookup tables and storing the shorthand phrases in the tables that reference the lookups in a foreign key. Mostly it's to have things readable, but I wonder about the performance and any down sides to doing that. – Caveatrob Sep 06 '12 at 20:50

0 Answers0