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).
<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?