Table design with Microsoft SQL Server

Question

I was hoping to get some advice from you folks on my table design methodologies (specifically in MS SQL if that matters).

Let me start by showing a couple of example tables that I might have in a typical application:

EntityType
-----------
EntityTypeID int identity (PK)
Token varchar(10) (Nonclustered IDX, UNIQUE constraint)
Name varchar(50)
Description varchar(255)
Sequence int (default 100)
CreatedDate datetime
CreatedBy varchar(100)
DeactivatedDate datetime null
DeactivatedBy varchar(100) null

Entity
-----------
EntityID int identity (PK)
EntityTypeID int not null (FK to EntityType)
CreatedDate datetime
CreatedBy varchar(100)
DeactivatedDate datetime null
DeactivatedBy varchar(100) null

Person
----------
PersonID int identity (PK)
EntityID int (FK to Entity)
FirstName varchar(100)
LastName varchar(100)
CreatedDate datetime
CreatedBy varchar(100) [or possibly a FK to a UserID int, too]
DeactivatedDate datetime null
DeactivatedBy varchar(100) null

I basically have two sort of table "templates" (by templates, I mean muscle memory in my fingers when I'm writing "CREATE" statements).

They're:

<TableName>ID int not null identity,
<Columns>,
CreatedDate datetime not null,
CreatedBy varchar(100),
DeactivatedDate datetime null,
DeactivatedBy varchar(100) null

And for some tables (mostly lookup tables), the addition of a "Token" and a "Sequence" column.

Specific column notes:

"Token" column is so I can easily reference it in application code (C#) with a "named" key instead of a sequential PK which could be different across multiple database instances. I haven't been too consistent here, sometimes I'll use CHAR, VARCHAR, or UNIQUEIDENTIFIER types. So if there's ever a situation that comes up where I need to reference a specific record in appcode, I would never hardcode a PK, but I may hardcode (via code or configuration) a "Token". INT PKs remain for FK references and automatic appcode where I never need to hardcode something (like a list of records).

"Sequence" column is used by my ORDER BY's. E.g. ORDER BY Sequence, Name. If all "Sequence" fields are the same, which by default they will be 100, it will fallback to sorting by Name. This is handy for tables like Country where we might want to float "USA" to the top (for example) but keep everything else sorted alphabetically.

"DeactivatedDate" column is always used in queries as an "IsActive" flag (WHERE DeactivatedDate IS NULL OR DeactivatedDate > getdate()). It allows for recording audit trails or offers the ability to expire a record at a later time.

Basically: first come the columns used as identifiers (PK, Token), all other columns, and lastly the remaining "template" columns (the occasional Sequence and pretty much always some sort of Audit columns for Created/Deactivated data).

Now that I've bored you with all that, my questions are:

• Is this a good design methodology?
  • Or, is there an alternative design methodology where I can offload some of these columns elsewhere so I don't have to make sure I name everything correctly (else my OCD kicks in and I must fix it at all costs lol)
• Is having a Token (secondary key) column, meant specifically for situations where app code needs to lookup or reference a specific record, a common practice?
  • Is there a better name than "TOKEN"?
  • Is there a recommended data type I should stick with for the token?
• Any other suggestions for how I maintain audit data and handle references to specific records from within appcode?

Thanks!

Answer 1

This all looks like pretty standard stuff. In addition I'd be inclined to add Modified columns in addition to Created ones if the data often changes.

Token is quite common - especially for high volume systems where the DB can become a bottleneck. An alternative key is generated and used as a pseudo key until such time as a proper key can be generated. A GUID is usually used for this sort of thing (uniqueidentifier in SQL Server speak). Please note that although these are unlikely to collide, they do depend on the hardware that generated them so if you have more servers generating them then this could be problematic.

The only issue I can see is the ...By columns. If you're storing names here - I'd be inclined to store the key here rather than the name.

Answer 2

My two cents:

Is this a good design methodology?

Looks pretty good to me. Looks like it have matured for some time.

sequence column for ordering

The order sequence is a very good idea in most top level tables. I would use a different name thought since "sequence" is also the name of some database objects that are used to provide consecutive numbers for PKs. I use "sort_order" or "order".

CreatedDate, CreatedBy, DeactivatedDate and DeactivatedBy

I would have another table were multiple status changes are logged, although I recognize that could be overkill in some applications and probably an audit log table (or file) suffices, it all depends of the needs and business rules and whether or not the app should provide such information or it is for internal purposes only.

Is having a Token (secondary key) column, meant specifically for situations where app code needs to lookup or reference a specific record, a common practice?

Sometimes there's a business key than can be used as token. Your mention of how yo use your tokens makes me wonder how do you make it consistent across databases? Do you use a basic, pre-populated database as template for new ones?

I guess some of the tables you would need a "token" for are top level tables, the ones that populate drop-down lists or are otherwise used to categorize rows in other tables. Try as much as you can to use business values, like for example if the table is "AIRPORTS" you can use the IATA airport code, of if the table is LANGUAGES you can use the ISO standards, if naming such a column "TOKEN" for every table helps you, that's OK. I would use another nomenclature like airport_code, language_code, company_code etc.

Any other suggestions for how I maintain audit data and handle references to specific records from within appcode?

Not every historical data is necessarily an audit table although it's auditable. Depending of the data it's necessary to keep a historical of changes and who did what when, and not just the very last action and as such a many-to-many join table is needed. In those cases a plan for transferring old data to another database has to be made so data is not stored forever in the main database.