We mostly store the ordinal value of the enumerated type variable to an int in the database.
Yes, it is fragile with regards to people changing the enumeration - but we have guidelines and unit tests to avoid that.
type
TPSDExpeditionType = (etRetailer,etWholesaler,etRetailerDairy,etWholesalerDairy); // Do not reorder or remove - add at end
TLocation = class
ExpeditionType: TPSDExpeditionType;
We use RTTI to export the enums to a view in the database so that we can do logic in the SQL code using the same identifiers as we use in Delphi.
T-SQL doesn't do constants per se, but selecting from a flat static view is optimized by the query engine and is extremely low cost.
CREATE TABLE [dbo].[t_locations](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ExpeditionType] [int] NOT NULL
--
CREATE VIEW [dbo].[v_psd_constants]
AS
SELECT 0 AS etRetailer, 1 AS etWholesaler, 2 AS etRetailerDairy, 3 AS etWholesalerDairy
--
SELECT * FROM v_Locations loc WHERE loc.ExpeditionType = (SELECT TOP(1) etRetailerDairy FROM v_psd_constants)
This means we can handle renames and additions - but not removals or reordering.
From an SQL performance perspective, this runs circles around using strings.