如何保持诚信？
我只是好奇你们如何创建表格来保持完整性？
tblUserProfile
UserId
EyeColorId
HairColorId
RelationShipStatusId
。
每个值（例如 EyeColorId）都有一组值，例如棕色、黑色、黄色、绿色 类似的，HairColorId 有黑色、金发、深红色等，RelationShipStatusId 有离婚、已婚、单身等。我应该为其中每一个创建不同的表吗？就像
tblEyeColor
EyeColorId
EyeColorCode
那时一样:-
tblHairColor
HairColorId
HairColorCode
同样继续创建表格？这样的桌子有很多（大约20-25张）。如果我继续创建这些表并对它们进行连接，这将严重降低我的性能。你们如何维护这种枚举值？我应该保留检查约束还是应该继续制作表格？
I am just curious how do you all create tables to maintain integrity?
tblUserProfile
UserId
EyeColorId
HairColorId
RelationShipStatusId
etc.
Each of these values like EyeColorId has a set of values like Brown,Black,Yellow,Green. Similary HairColorId has black, blonde, crimson etc and RelationShipStatusId has Divorced, Married, Single etc. Should i create different tables for each one of these? like
tblEyeColor
EyeColorId
EyeColorCode
then :-
tblHairColor
HairColorId
HairColorCode
and likewise keep creating tables? There are many such tables(approximately 20-25). If i keep creating these tables and make joins on them, it will terribly slow down my performance. How do you all maintain this sort of enum values? Should i keep a check constraint or should i keep making tables?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我想说 Color 看起来像是一个头发和眼睛都可以使用的表。强制执行“任何人都不应该有金发或蓝头发”这一事实对于您的正直有多重要？
有些人采用单个查找表的想法，该表包含一个 id、一个组和一个值，可能还有每行的描述。 id 和 group 将是主键，因此头发的 id = 1 和 group = 1 作为 BLONDE 头发颜色，眼睛的 id = 1 和 group = 2 作为 HAZEL 眼睛颜色，等等。
其他人会嘲笑这是一个糟糕的非规范化设计。
当特定查询的数量变大时，连接只会减慢您的速度。
我的建议是进行标准化设计，直到有一些数据表明其性能不佳为止。发生这种情况时，请分析您的应用程序以找出问题所在，并适当重构或反规范化。
我想说索引对性能的影响比这些 JOIN 更大。如果没有数据支持，您可能会犯过早优化的错误。
I would say that Color looks like it could be a single table that both hair and eye could both use. How important is it to your integrity to enforce the fact that no one should have blonde eyes or blue hair?
Some people go with the idea of a single lookup table, which would have an id, a group, and a value and perhaps a description for each row. The id and group would be the primary key, so you'd have id = 1 and group = 1 for hair as the BLONDE hair color, id = 1 and group = 2 for eyes as the HAZEL eye color, etc.
Others would deride this as a poor denormalized design.
The joins will only slow you down when the number for a particular query gets large.
My advice would be to do a normalized design until you have some data to suggest that it's performing poorly. When that happens, profile your app to find out where the problem is and refactor or denormalize appropriately.
I would say that indexing will have a greater impact on your performance than these JOINs. You might be guilty of premature optimization without data to support it.
如果选项数量
固定，则无需创建表格。您可以在表格中使用Enum类型。例如，
EyeColor列将是Black、Brown、Blue的枚举，但是我从未见过绿眼睛的人。哈哈
There is no need of creating tables if the number of options are
fixedYou can useEnumType instead in your table.e.g. Column
EyeColorwill be Enum ofBlack, Brown, BlueHowever I've never seen someone with Green Eyes. LOL
这是传统的方法，是的。
大多数现代 RDBMS 都会长时间缓存这些查找之类的小表，从而缓解潜在的多连接问题。
此外，您可以在应用程序中预加载所有查找表，并在访问数据库之前将枚举反向转换为代码中的 id。这样您就不需要在查询中加入。
That is the traditional method, yes.
Most modern RDBMSs will cache small tables like these lookups for extended periods of time, which alleviates the potential multi-join issues.
Also, you could pre-load all of the lookup tables in your application, and reverse-convert the enums to ids in code before accessing the database. This way you won't need joins in your queries.