CODEWITHSUNDEEP
c#sql-serverdatabaset-sqlenums
user1070241
user1070241

Reputation:

Requirement change: Use database table instead of enums. How to deal with existing records that use enum value?

So requirements (actually the law) have changed, and the client wants to be able to add/remove values from what is currently an enum. So I need to create a database table instead that allows the client to dynamically deal with the list of values at runtime.

Currently there is a table in my database called tblJob, this table has a column PaymentTypes of type int which corresponds to the enum that has been used. So every time a new job is created in the system, the user selects a PaymentType from this enum list, and it is stored as a new record in tblJob

To deal with the new requirements, I have added a new table tblPaymentTypes, and hardcoded the current enum list into the table. Each value here has a unique GUID identifier. That's all good.

Now, for each new job, the PaymentType options are retrieved from the database and the user selects one. However, I need to change the type of the column PaymentTypes in the table tblJob to allow for GUID values instead of integers.

I could do this but what about all the existing records who all have a integer value as the PaymentType ?

From MSDN on modifying column types with existing data in them:

Modifying the data type of a column that already contains data can result in the permanent loss of data when the existing data is converted to the new type. In addition, code and applications that depend on the modified column may fail. These include queries, views, stored procedures, user-defined functions, and client applications. Note that these failures will cascade. For example, a stored procedure that calls a user-defined function that depends on the modified column may fail. Carefully consider any changes you want to make to a column before making it.

Which makes perfect sense.

To avoid this question being too localized, these are the general questions I'm asking:

Any help/pointers are appreciated.

Upvotes: 1

Views: 330

Answers (1)

David
David

Reputation: 218837

Why do the identifiers need to be GUIDs? If the lookup table simply uses integer identifiers (as most tables generally do) then the initial population of records in that table can line up exactly with the enum's integer values. Then no data migration is necessary, simply add the foreign key relationship from tblJob to tblPaymentTypes and you're done.

If you must use GUIDs (though I still contend that you shouldn't, if for no other reason than they make terrible clustered indexes) then you're going to have a data migration process which will likely consist of these steps:

  1. Add the tblPaymentTypes table and data.
  2. Add a GUID column to tblJob, nullable, no foreign key.
  3. Update tblJob to set every value of the new column to the corresponding value from the enum (likely a hard-coded mapping because it's a one-time script).
  4. Make the new column non-nullable and a foreign key to tblPaymentTypes.
  5. Drop the column from tblJob which contained the integer enum values.
  6. Update consuming code to use the new column instead of the old column.

Actually executing these steps should be pretty quick if it's all planned and scripted ahead of time, you'll likely just have a moment or two of downtime in the process. (You'll want to isolate the database from the application when migrating data like this to avoid any kind of data corruption from any mid-migration state change.) But you can see how it would be a lot easier to just use the same data type you already have.

Upvotes: 2

Related Questions