数据库设计：我应该在表或新表中使用约束 [英] DB design: Should I use constraints within a table or a new table

问题描述

我继承了一个现有的大型数据库，我想知道是否应该重构，因为95％的查询需要加入至少4个表。

数据库有5个表，只有一个ID和名称列少于20行。我假设作者做了这个，所以他可以改变那里的名字，而不是在其他表中更改它们，但是许多这些表仅在另一个表中引用。我应该将这些小的2列表重构到一个较大的表中，并向列添加一个约束，以便用户不能输入不正确的名称，而不是具有单独的表？

解决方案

抵制那种冲动。从你的描述我可以推断，现有的设计是坚实的，可能正常化。

如果您在查询中写入了很多联接，我建议您创建视图，否则您的重构实际上可能会撤消一个良好的数据库结构。为了减轻样板。

...作者做了这个，所以他可以改变名字在那里没有更改
他们在其他表...

这是证明设计良好，正是您应该在规范化数据库中努力的证据。

I inherited a large existing DB and I'd like to know if I should refactor it because 95% of my queries require joining at least 4 tables.

The DB has a 5 tables that only have an ID and Name column with less than 20 rows. I assume the author did this so he could change the names there and not change them in the other tables, but many of those tables are only referenced in one other table. Should I refactor these small 2 column tables into the a larger table and add a constraint to the column so users can't input incorrect names instead of having seperate tables?

解决方案

Resist that urge. From your description I can deduce that the existing design is solid and probably well normalized. Your refactoring may actually undo a good db structure.

If you are bothered by writing a lot of joins in your queries I would suggest creating views to mitigate the boilerplate.

...the author did this so he could change the names there not change them in the other tables...

That is evidence of good design and exactly what you should strive for in a normalized database.

这篇关于数据库设计：我应该在表或新表中使用约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！