数据库设计 - 多个查找/枚举表或一个大表？ [英] Database Design - Multiple Lookup/Enum Tables or One Large Table?

问题描述

我有很多表使用Lookup / Enum引用大部分的列值。
例如：


个人表 - PersonID | RaceCode | HairColorCode | HairStyleCode | TeethConditionCode


位置表 - LocationID | SizeCode | OutsideColorCode |条件码


像种族，大小，颜色，条件等等的东西只是对代码查找表的外键引用。此代码表有其他字段，但对我的问题不重要。数据库用于SaaS应用程序，这意味着每个客户端都可以有自己的颜色，种族，条件等列表。有些代码将是静态的，客户端不能改变。




最好是有1个代码表或2种类型的代码表（DynamicCodeTable为客户定义的，StaticCodeTable为那些改变）或我应该每个代码类型的表（RaceCodeTable，HairColorTable，Condition等）？




我最担心的是所有的sql连接。我正在使用的Person表具有20+以上的代码属性。加入20个不同的表VS加入同一个表时，性能有差异吗？有多个表格意味着每个表格都会更小，并且查找应该花费更少的时间。但是，一张桌子也可以很快。任何建议？

解决方案

不知道有关应用程序或要求的更多信息，我建议为每个代码类型提供一个表。 IMO的数据库设计将更加清晰，并且自我记录，为您拥有的每种类型的代码拥有外键。

I have many tables that use Lookup/Enum references for most of their column values. For example:
Person Table - PersonID | RaceCode | HairColorCode | HairStyleCode | TeethConditionCode
Location Table - LocationID | SizeCode | ExteriorColorCode | ConditionCode
Things like Race,Size,Color,Condition,etc would just be foreign key references to a Code lookup table. This code table has other fields but aren't important for my question. The database is for a SaaS application which means each client could have their own list of of Colors, Races, Conditions, etc. There are some codes that would be static which clients couldnt change.

Is it better to have 1 code table or 2 types of code tables (DynamicCodeTable for customer defined ones and StaticCodeTable for those that one change) or should I have a table for each code type (RaceCodeTable, HairColorTable, Condition, etc) ?

The thing I am the most worried about is all the sql joins. The Person table I am working with has 20+ of these code attributes. Is there a difference in performance when joining to 20 different tables VS joining to the same table 20 times? Having multiple tables means each table would be smaller and the lookup 'should' take less time. But having a single table could be quick too. Any suggestions?

解决方案

Without knowing more about the application or requirements I would recommend having one table for each code type. IMO the database design would be more clear and self documenting to have foreign keys for each type of code you have.

这篇关于数据库设计 - 多个查找/枚举表或一个大表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！