您是否喜欢“查找"您的数据模型中的表? [英] Do you like a "Lookup" table in your data models?

问题描述

您喜欢数据模型中的查找"表吗?

Do you like a "Lookup" table in your data models?

在设计新的数据模型时，我们可以使每个实体具有类型，状态和其他表，也可以将所有表分组到一个查找"表中.您有偏好吗?是赞成还是反对?例如

In designing a new data model, we can either have type, status, and other tables per entity, or all grouped together in one "Lookup" table. Do you have a preference? An argument for or against? E.g.

选项1 ...

表:员工

EmployeeId  EmployeeName  EmployeeStatusId  EmployeeTypeId  EmployeeGenderId
123         Bob           234               345             456

表:EmployeeStatus

Table: EmployeeStatus

EmployeeStatusId  EmployeeStatusName
234               Active
235               Inactive
236               Other

表:EmployeeType

Table: EmployeeType

EmployeeTypeId  EmployeeTypeName
345             W2
346             1099
347             Other

表:EmployeeGender

Table: EmployeeGender

EmployeeGenderId  EmployeeGenderName
456               Male
457               Female
458               Other

选项2 ...

表:员工

EmployeeId  EmployeeName  LookupEmployeeStatusId  LookupEmployeeTypeId  LookupEmployeeGenderId
123         Bob           800                     803                   806

表:查找

LookupId    LookupGroup     LookupValue
800         EmployeeStatus  Active
801         EmployeeStatus  Inactive
802         EmployeeStatus  Other
803         EmployeeType    W2
804         EmployeeType    1099
805         EmployeeType    Other
806         EmployeeGender  Male
807         EmployeeGender  Female
808         EmployeeGender  Other

推荐答案

您不能使用选项#2进行声明性引用完整性，这实际上是通过首先声明这些表而获得的主要好处.

You can't do declarative referential integrity with option #2 which is really the main benefit you get by declaring these tables in in the first place.

这篇关于您是否喜欢“查找"您的数据模型中的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！