Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
336 views
in Technique[技术] by (71.8m points)

sql - 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.

OPTION 1...

Table: Employee

EmployeeId  EmployeeName  EmployeeStatusId  EmployeeTypeId  EmployeeGenderId
123         Bob           234               345             456

Table: EmployeeStatus

EmployeeStatusId  EmployeeStatusName
234               Active
235               Inactive
236               Other

Table: EmployeeType

EmployeeTypeId  EmployeeTypeName
345             W2
346             1099
347             Other

Table: EmployeeGender

EmployeeGenderId  EmployeeGenderName
456               Male
457               Female
458               Other

OPTION 2...

Table: Employee

EmployeeId  EmployeeName  LookupEmployeeStatusId  LookupEmployeeTypeId  LookupEmployeeGenderId
123         Bob           800                     803                   806

Table: Lookup

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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...