1

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!

4

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.

  • Thanks Robbie, yeah I didn't make it too clear. There's one small note next to ONE of the "By" columns in my mock schema that mentions they will sometimes be int FK's, too - easy to miss, I should have put that in my column notes. The particular system I'm working with now is an AD-based Windows Client app, so it was a little trickier. I just ended up storing Windows login names, instead of a GUID or SID, but in general (at least on the web), I would have a User table and a UserID stored in those. Thanks for the advice. – Adam Plocher Apr 20 '18 at 9:58
  • Oh, and with regard to the "Modified": I think that's a good idea. I've done it in the past, not sure why I haven't made a habit of it. In fact, some of these tables have an ongoing changelog from a trigger that records all changes to an identical table with "History" appended to the name and 2 additional cols: ChangeType (char(1) = [i]ns,[u]pd,[d]el)/ChangeDate. If I was to pick 1 column, Modified would be more appropriate terminology anyway since it could cover ins, upd, and del. So yeah, I should go with all 3 or just 1 in those cases I guess. Having 2 (ins, del) doesn't make sense... – Adam Plocher Apr 20 '18 at 10:03
  • @AdamPlocher Ah, OK - login names should be unique and so are a key of sorts. Just saw the varchar and assumed it was a name of some sort. – Robbie Dee Apr 20 '18 at 10:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.