Hello,I need to create a few lookup tables and I often see the following:create table Languages( Id int identity not null primary key (Id), Code nvarchar (4) not null, Description nvarchar (120) not null,);create table Posts( Id int identity not null primary key (Id), LanguageId int not null, Title nvarchar (400) not null,);insert into Languages (Id, Code, Description) values (1, "en", "English");
This way I am localizing Posts with language id ...IMHO, this is not the best scheme for Languages table because in a Lookup table the PK should be meaningful, right?So instead I would use the following:create table Languages( Code nvarchar (4) not null primary key (Code), Description nvarchar (120) not null,);create table Posts( Id int identity not null primary key (Id), LanguageCode nvarchar (4) not null, Title nvarchar (400) not null,);insert into Languages (Code, Description) values ("en", "English");The NET applications usually use language code so this way I can get a Post in English without using a Join.And with this approach I am also maintaining the database data integrity ...This could be applied to Genders table with codes "M", "F", countries table, transaction types table (should I?), ...However I think it is common to use int as PK in lookup tables because it is easier to map to ENUMS.And know it is even possible to map to Flag Enums so have a Many to Many relationship in an ENUM.That helps in NET code but in fact has limitations. A Languages table could never be mapped to a FLags Enum ...... An flags enum can't have more than 64 items (Int64) because the keys must be a power of two.A SOLUTIONI decided to find an approach that enforces database data integrity and still makes possible to use enums so I tried:create table Languages( Code nvarchar (4) not null primary key (Code), Key int not null, Description nvarchar (120) not null,);create table Posts( Id int identity not null primary key (Id), LanguageCode nvarchar (4) not null, Title nvarchar (400) not null,);insert into Languages (Code, Key, Description) values ("en", 1, "English");With this approach I have a meaningfully Language code, I avoid joins and I can create an enum by parsing the Key:public enum LanguageEnum { [Code("en") English = 1}I can even preserve the code in an attribute. Or I can switch the code and description ...What about Flag enums? Well, I will have not Flag enums but I can have List ...And when using List I do not have the limitation of 64 items ...To me all this makes sense but would I apply it to a Roles table, or a ProductsCategory table?In my opinion I would apply only to tables that will rarely change over time ... So:Languages, Countries, Genders, ... Any other example?About the following I am not sure (They are intrinsic to the application):PaymentsTypes, UserRolesAnd to these I wouldn't apply (They can be managed by a CMS):ProductsCategories, ProductsColorsWhat do you think about my approach for Lookup tables?
Proper use of a Lookup table and enum
Posted - 2014-09-10 : 07:36:06
