低耦合和 SQL 连接
假设我有一个表people（id，firstname，lastname）。
还有另外两个表应包含这些字段，因此我们将重用人员表：users (id, username, person_id) 和 companys (id, name, contact_person_id)代码>.
现在要获取公司或用户，我们必须加入人员表。如果我们更改 people 表，我们必须重写所有查询，可能还需要重写大量代码。
这是一个真正的问题吗？我的数据库结构有缺陷吗？有没有办法保持低耦合，比如 ORM？
谢谢大家的回答。
Say I have a table people (id, firstname, lastname).
There are two other tables that should contain these fields, so we'll just reuse the people table: users (id, username, person_id) and companies (id, name, contact_person_id).
Now to get companies or users we must join the people table. If we change the people table, we must rewrite all queries, and probably lots of code.
Is this a real problem? Is my DB structure flawed? Is there a solution to maintain low coupling, like maybe ORM?
Thank you for all anwers.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
耦合是一个植根于软件模块的概念。
我没有看到与 SQL 的相关性。
由于两个表都位于同一服务器中，因此它们已经耦合（就使用服务器的软件而言）。我只是不明白你想要实现什么低耦合。
来自维基百科：
Coupling is a concept that is rooted in software modules.
I don't see the relevance to SQL.
Seeing as both tables live in the same server, they are already coupled (so far as the software using the server is concerned). I just don't see what low coupling you are trying to achieve.
From wikipedia:
您的外键可以轻松访问
people表中的数据。是的，如果people表发生更改，可能需要更改，但是更改影响您的 JOIN 的内容意味着您的要求已更改。换句话说，需要以影响 JOIN 的方式更改名字或姓氏是不现实的。这不是一个真正的问题。
上面表示的是数据库规范化的结果，这是常见且良好的实践。通过将表视为单独的实体（因为它们很可能是与软件的逻辑关系），它确实引入了表之间的耦合，但它的设计目的实际上是简化和提高可扩展性。
这是很好的数据库设计。
Your foreign keys enable easy access to the data in the
peopletable. Yes, it may require changes if thepeopletable is changed, but changing something that affects your JOINs implies that your requirements have changed.In other words, it is not realistic to need to change firstname, or lastname in a way that affects your JOINs. This is not a real problem.
What is represented above is the result of Database Normalization, which is common and good practice. By thinking of your tables as separate entities, as they very well may be in terms of logical relationships to the software, it does introduce coupling between tables, but it's by design to actually simplify and improve scalability.
This is good database design.
大多数时候，所做的修改不会造成破坏，例如添加新列。几乎没有进行过重大更改，例如修改列名称或数据类型。
关系数据库管理系统允许创建特殊的数据类型，这使得某些修改变得更加容易。如果 FirstName 和 LastName 被定义为用户定义类型 PersonName，则更改类型将使相同的更改出现在使用这些列的所有查询和存储过程中。不幸的是，几乎没有人使用过用户定义的数据类型。
如果从概念上讲，作为用户和公司事物一部分的称为“人”的事物确实代表了一个连贯的想法，那么对人的更改将不会具有破坏性，因为任何地方都需要所需的更改。另一方面，如果这是为了方便起见而将概念上不同的事物拼凑在一起，那么您可能会在以后遇到问题。
Most of the time, the kind of modifications that would be made would not be disruptive, such as adding new columns. Breaking changes, like modifying column names or data types, is hardly ever done.
Relational database management systems allow the creating of special data types which make certain modifications much easer. If FirstName and LastName were defined as a user-defined type PersonName, then changing the type would make the same change appear in all the queries and stored procedures that use the columns. Unfortunately, hardly anyone ever uses user-defined data types.
If, conceptually, the thing called "Person" that is part of the things User and Company really represents a coherent idea, then changes to Person will not be disruptive, because any changes that are needed are needed everywhere. If, on the other hand, this is hacking conceptually dissimilar things together for convienience sake, then you are likely to encounter problems down the road.
不，您的结构在这种情况下没有缺陷。你对它的认识是有缺陷的。
表名和列名构成数据库公共接口的一部分。将其视为 API。不管你写什么样的代码，如果你改变了一个API，你就必须重写一些代码。如果您更改数据库 API（表和列名称），您可能必须重写大量代码。但 。 。 。
假设您从版本控制系统中检查数据库代码，并将“people”表中的列名称更改为first_name、last_name。如果没有任何其他更改，您将无法重建数据库，因为您已经破坏了公共接口。 （选择“firstname”的视图将终止构建。读取或写入“firstname”的存储过程将终止构建。）
但是您可以通过重命名“people”表并创建视图来快速恢复。你可能就这样往前走。
SELECT * FROM people;)两个已更改的列，别名
名字到名字，以及
姓氏到姓氏。
可更新视图，随便写
您需要制作的程序代码
视图可更新。
任何期望查询或更新名为“people”的基表的代码都将查询和更新名为“people”的视图。不需要重写其他代码。 （除非您的代码对是否针对基表进行操作做出了无根据的假设。）
关系数据库通过可更新视图实现逻辑数据独立性。
No, your structure isn't flawed in this context. Your perception of it is flawed.
Table names and column names make up part of the public interface to a database. Think of it as an API. No matter what kind of code you write, if you change an API, you're going to have to rewrite some code. If you change a database API--table and column names--you might have to rewrite a lot of code. But . . .
Let's say you check out your database code from your version control system, and you change the column names in your "people" table to first_name, last_name. Without any other changes, you won't be able to rebuild the database, because you've broken the public interface. (Views that select "firstname" will kill the build. Stored procedures that read or write to "firstname" will kill the build.)
But you can quickly recover from that by renaming the "people" table and creating a view. You might go forward like this.
SELECT * FROM persons;)two changed columns, aliasing
first_name to firstname, and
last_name to lastname.
updatable views, write whatever
procedural code you need to make the
view updatable.
Any code that expected to query or update a base table named "people" will instead be querying and updating a view named "people". No other code needs to be rewritten. (Unless you have code that makes unwarranted assumptions about whether it's operating against a base table.)
Relational databases implement logical data independence through updatable views.