How to Manage User Modifiable Lookup Tables

Question

There is a table in our database that acts very much like a standard lookup table (ID, Description). However, this particular one is not static, the client wants the ability to add entries on the fly. Some entries that will come pre-populated are "special" in that there will be code that checks for them (various business rules).

Normally, I'd create the table without auto-incrementing IDs so I can be safe in the knowledge that the enum that mirrors the entries in the table always match. Then it's just a matter of checking if this object's ID matches up with the enum value I'm checking for.

I could attempt the same approach, with IDs that don't auto-increment and an enum that only covers the entries that aren't added on the fly. We would shortly run into the issue of coming up with the next ID when the user adds a new entry. Basically re-implementing in code the database's auto-increment feature.

If I switch to using identity columns, there'd be the whole issue of getting out of sync with the enum values.

Of course I could always match on the textual 'Description' attribute but that's bad for obvious reasons.

Is there a nice way to deal with something like this? This question doesn't really answer it for me.

Answer 1

1) Assign a range to your client that is larger than the number of values your application will ever need, say 1000000. Add a trigger to enforce only allowing new values above that range.

2) Use auto-increment and generate your enums off your local copy of the database.

Answer 2

Why not just use two tables? One table holds your ENUM values that you code for. The other handles all user configurable items.

Unless that is you are creating new enums based on the client entered values as well. If that's the case why don't you migrate your primary key to a GUID and use a static class with static string members (kind of like a virtual ENUM). Then you would not need to worry about uniqueness as the guids are much harder to duplicate unless you do it on purpose.

We use the GUID psuedo enum approach as we have to maintain multiple copies of the same database and they can easily get out of sync. The guids help in that regard.

Answer 3

Building on Mitch's answer:

You can seed the identity column with the large value and when you're populating the table with the predetermined identities, you can set identity insert on.

CREATE TABLE dbo.Table_1
(
    ID int NOT NULL IDENTITY (1000000, 1),
    Label nvarchar(50) NOT NULL
)  ON [PRIMARY]
GO

SET IDENTITY_INSERT dbo.Table_1 ON
GO

INSERT INTO dbo.Table_1(ID, Label) VALUES (1, 'First');
INSERT INTO dbo.Table_1(ID, Label) VALUES (2, 'Second');

Answer 4

To be honest, that smells like one concern servicing two different requirements.

I would separate it into two tables, something like ApplicationLookups and CustomLookups, and then it would be intuitive to treat them differently in code and also from a DB perspective.

Answer 5

In addition to the solutions given here, there is always the possibility to use a completely meaningless identity for all your lookup foreign keys but also have a column which links the lookup to your enum values for business logic:

lkpTable
PK Identity
Description
FK LogicEnum NULL

lkpLogic
PK EnumValue
LogicParamColumns

In this case, the logic is provided and not altered by users. New lookups can even be routed to use any existing logical rules - so you can have different settings which behave the same way as existing hardcoded business rules, but display differently.