为“business_owners”设置数据库和“客户”
我正在建立一个包含“business_owners”和“customers”的数据库。我可以在几天内完成此设置，但想看看您对最佳实践的看法。
我可以有两个表，“business_owners”和“customers”，每个表都有姓名、电子邮件等，或者...
我可以做一个表“Users”，并将 user_type 设置为“business_owner”或“customer”，然后使用该类型以确定要显示的内容。
我认为第二个选项是最好的，有什么反馈吗？
I'm setting up a database that will have 'business_owners' and 'customers'. I could set this up in a couple days but wanted to see what your opinion is on best practice.
I could have two tables, 'business_owners' and 'customers', each with name, email etc. or...
I could do one table 'Users' and have a user_type as 'business_owner' or 'customer' and just use that type to determine what to show.
I'm thinking the second option is best, any feedback?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
经验法则：
如果您有多个具有相同（或接近相同）列的表，则应将它们压缩为一个表。根据需要使用类型代码等进行区分，并为依赖于类型代码的列制定业务规则。
答：
第二种选择是最好的方法。它是最具可扩展性的，如果您需要使用包含企业所有者和企业主的结果集，它将是最容易使用的。顾客。
Rule of thumb:
If you have more than one table with identical (or near identical) columns, they should be condensed into a single table. Use a type code/etc to distinguish between as necessary, and work out the business rules for columns that depend on the type code.
Answer:
The second option is the best approach. It's the most scalable, and will be the easiest to work with if you ever need to use resultsets that include both business owners & customers.
这取决于两种类型之间的差异，如果它们除了作为“用户”或“企业主”的角色之外具有完全相同的属性，我建议采用第二个选项，以避免在具有相同列方面过度杀伤2张独立的桌子。
It depends on the difference between the two types, if they share exactly the same attributes aside from their role as either a 'user' or 'business owner' I would suggest going for the second option to avoid overkill in terms of having identical columns in 2 separate tables.
您将如何在对象模型中对此进行建模？您是否会建立一个单一的超类（称为“利益相关者”）来捕获企业主和客户的属性？然后，您会设置专门的子类“企业主”和“客户”来扩展利益相关者的定义吗？如果是这样，请继续阅读。
您的案例看起来像是 Gen-Spec 设计模式的一个实例。 Gen-spec 通过超类-子类层次结构为面向对象程序员所熟悉。不幸的是，关系数据库设计的介绍往往会跳过如何为 Gen-Spec 情况设计表。幸运的是，它很好理解。在网络上搜索“关系数据库泛化专业化”将产生几篇关于该主题的文章。您的一些点击将是之前关于 SO 的问题。这里有一篇文章根据对象关系映射讨论了 Gen-Spec。
诀窍在于子类（专用）表的 PK 的分配方式。它不是由某种自动编号功能生成的。相反，它是超类（通用）表中 PK 的副本，因此是对其的 FK 引用。
因此，如果情况是车辆、卡车和轿车，则每辆卡车或轿车都会在车辆表中拥有一个条目，卡车也会在卡车表中拥有一个条目，其 PK 是车辆表中相应 PK 的副本。轿车和轿车桌也是如此。只需进行联接即可轻松确定车辆是卡车还是轿车，而且您通常希望在此类查询中联接数据。
How would you model this in an object model? Would you set up a single superclass, call it "stakeholders", that captures the properties of both business-owners and customers? Would you then set up specialized subclasses, "business-owner" and "customer" that extend the definition of stakeholders? If so, read on.
Your case looks like an instance of the Gen-Spec design pattern. Gen-spec is familiar to object oriented programmers through the superclass-subclass hierarchy. Unfortunately, introductions to relational database design tend to skip over how to design tables for the Gen-Spec situation. Fortunately, it’s well understood. A web search on “Relational database generalization specialization” will yield several articles on the subject. Some of your hits will be previous questions here on SO. Here is one article that discusses Gen-Spec in terms of Object Relational Mapping.
The trick is in the way the PK for the subclass (specialized) tables gets assigned. It’s not generated by some sort of autonumber feature. Instead, it’s a copy of the PK in the superclass (generalized) table, and is therefore an FK reference to it.
Thus, if the case were vehicles, trucks and sedans, every truck or sedan would have an entry in the vehicles table, trucks would also have an entry in the trucks table, with a PK that’s a copy of the corresponding PK in the vehicles table. Similarly for sedans and the sedan table. It’s easy to figure out whether a vehicle is a truck or a sedan by just doing joins, and you usually want to join the data in that kind of query anyway.