SQL“快捷方式”标识符还是一长串连接？
问题：是否可以在表中使用“快捷方式”标识符，这样我就不必执行一长串连接来获取我需要的信息？
为了理解我在说什么，我必须在这里举一个例子，它看起来相当复杂，但我在这里已经简化了问题很多，并且它应该很容易理解（我希望）。
基本设置：“公司”可以是“附属机构”、“客户端”或两者兼而有之。每个“公司”可以有多个“联系人”，其中一些可以是登录的“用户”特权。
`Company` table ---------------------------------------------- ID Company_Name Address -- ----------------------- ----------------- 1 Acme, Inc. 101 Sierra Vista 2 Spacely Space Sprockets East Mars Colony 3 Cogswell Cogs West Mars Colony 4 Stark Industries Los Angeles, CA
我们的数据库中有四家公司。
`Affiliates` table --------------------- ID Company_ID Price Sales -- ---------- ----- ----- 1 1 50 456 2 4 50 222 3 1 75 14
每个公司都可以有多个联属会员 ID，以便他们可以向不同市场展示不同定价级别的产品。
我们的两家公司是附属公司（Acme, Inc. 和 Stark Industries），Acme 有两个附属 ID。
`Clients` table -------------------------------------- ID Company_ID Referring_affiliate_id -- ---------- ---------------------- 1 2 1 2 3 1 3 4 3
每个公司只能成为客户一次。
我们的三个公司是我们的客户（Spacely Space Sprockets、Cogswell Cogs 和 Stark Industries，也是我们的附属公司）
在所有这三种情况下，他们都是由 Acme, Inc. 使用其两个附属 ID 之一向我们推荐的。
`Contacts` table ----------------------------------------- ID Name Email -- -------------- --------------------- 1 Wylie Coyote [email protected] 2 Cosmo Spacely [email protected] 3 H. G. Cogswell [email protected] 4 Tony Stark [email protected] 5 Homer Simpson [email protected]
每个公司至少有一个联系人，但在此表中，没有表明每个联系人为哪家公司工作，并且有还有一个额外的联系人（#5）。我们稍后会讨论这个问题。
这些联系人中的每一个可能有也可能没有系统上的登录帐户。
`Contacts_type` table
--------------------------------------
contact_id company_id contact_type
---------- ---------- --------------
1 1 Administrative
2 2 Administrative
3 3 Administrative
4 4 Administrative
5 1 Technical
4 2 Technical
将联系人与一个或多个公司关联。
每个联系人都与一个公司关联，此外，联系人 5 (Homer Simpson) 是 Acme, Inc 的技术联系人，联系人 4 (Tony Stark) 既是公司 4 (Stark Industries) 的管理联系人，又是技术联系人。公司 3 (Cogswell Cogs) 的联系人
`Users` table ------------------------------------------------------------------------------------- ID contact_id company_id client_id affiliate_id user_id password access_level -- ---------- ---------- --------- ------------ -------- -------- ------------ 1 1 1 1 1 wylie A03BA951 2 2 2 2 2 NULL cosmo BF16DA77 3 3 3 3 3 NULL cogswell 39F56ACD 3 4 4 4 4 2 ironman DFA9301A 2
用户表本质上是允许登录系统的联系人列表。
每个联系人有零个或一个用户；每个用户一个联系人。
联系人 1 (Wylie Coyote) 为公司 1 (Acme) 工作，是客户 (1) 也是关联公司 (1)
联系人 2 (Cosmo Spacely) 为公司 2 (Spacely Space Sprockets) 工作，是客户 (2)，但不是关联公司
等等...
现在终于解决问题了，如果有的话...
我是否通过Users<中的client_id和affiliate_id列进行循环引用/代码>表？这是一件坏事吗？我很难理解这个问题。
当有人登录时，它会根据用户表检查其凭据并使用 users.contact_id、users.client_id 、users.affiliate_id 进行快速查找，而不必将一串表连接在一起来查找相同的信息。但这会导致数据重复。
如果 users 表中没有 client_id，我必须像这样找到以下信息：
affiliate_id: join `users`.`contact_id` to `contacts_types`.`company_id` to `affiliates`.`company_id` client_id: join `users`.`contact_id` to `contacts_types`.`company_id` to `clients`.`company_id` company_id: join `users`.`contact_id` to `contacts_types`.`company_id` to `company`.`company_id` user's name: join `users`.`contact_id` to `contacts_types`.`contact_id` to `contacts`.`contact_id` > `name`
在每种情况下，我都不一定知道用户是否有条目在 affiliate 表或 clients 表中，因为它们可能只在其中一个表中拥有条目，而不是在两个表中都有条目。
是进行此类联接并通过多个表进行线程来获取我想要的信息更好，还是使用“快捷方式”字段来获取我想要的信息更好？
我有一种感觉，总的来说，这在某种程度上过于复杂，但我不明白如何。
我正在使用MySQL。
QUESTION: Is it okay to have "shortcut" identifiers in a table so that I don't have to do a long string of joins to get the information I need?
To understand what I'm talking about, I'm going to have to lay ouf an example here that looks pretty complicated but I've simplified the problem quite a bit here, and it should be easily understood (I hope).
The basic setup: A "company" can be an "affiliate", a "client" or both. Each "company" can have multiple "contacts", some of which can be "users" with log in privileges.
`Company` table ---------------------------------------------- ID Company_Name Address -- ----------------------- ----------------- 1 Acme, Inc. 101 Sierra Vista 2 Spacely Space Sprockets East Mars Colony 3 Cogswell Cogs West Mars Colony 4 Stark Industries Los Angeles, CA
We have four companies in our database.
`Affiliates` table --------------------- ID Company_ID Price Sales -- ---------- ----- ----- 1 1 50 456 2 4 50 222 3 1 75 14
Each company can have multiple affiliate id's so that they can represent the products at different pricing levels to different markets.
Two of our companies are affiliates (Acme, Inc. and Stark Industries), and Acme has two affiliate ID's
`Clients` table -------------------------------------- ID Company_ID Referring_affiliate_id -- ---------- ---------------------- 1 2 1 2 3 1 3 4 3
Each company can only be a client once.
Three of our companies are clients (Spacely Space Sprockets, Cogswell Cogs, and Stark Industries, who is also an affiliate)
In all three cases, they were referred to us by Acme, Inc., using one of their two affiliate ID's
`Contacts` table ----------------------------------------- ID Name Email -- -------------- --------------------- 1 Wylie Coyote [email protected] 2 Cosmo Spacely [email protected] 3 H. G. Cogswell [email protected] 4 Tony Stark [email protected] 5 Homer Simpson [email protected]
Each company has at least one contact, but in this table, there is no indication of which company each contact works for, and there's also an extra contact (#5). We'll get to that in a moment.
Each of these contacts may or may not have a login account on the system.
`Contacts_type` table
--------------------------------------
contact_id company_id contact_type
---------- ---------- --------------
1 1 Administrative
2 2 Administrative
3 3 Administrative
4 4 Administrative
5 1 Technical
4 2 Technical
Associates a contact with one or more companies.
Each contact is associated with a company, and in addition, contact 5 (Homer Simpson) is a technical contact for Acme, Inc, and contact 4 (Tony Stark) is a both an administrative contact for company 4 (Stark Industries) and a technical contact for company 3 (Cogswell Cogs)
`Users` table ------------------------------------------------------------------------------------- ID contact_id company_id client_id affiliate_id user_id password access_level -- ---------- ---------- --------- ------------ -------- -------- ------------ 1 1 1 1 1 wylie A03BA951 2 2 2 2 2 NULL cosmo BF16DA77 3 3 3 3 3 NULL cogswell 39F56ACD 3 4 4 4 4 2 ironman DFA9301A 2
The users table is essentially a list of contacts that are allowed to login to the system.
Zero or one user per contact; one contact per user.
Contact 1 (Wylie Coyote) works for company 1 (Acme) and is a customer (1) and also an affiliate (1)
Contact 2 (Cosmo Spacely) works for company 2 (Spacely Space Sprockets) and is a customer (2) but not an affiliate
etc...
NOW finally onto the problem, if there is one...
Do I have a circular reference via the client_id and affiliate_id columns in the Users table? Is this a bad thing? I'm having a hard time wrapping my head around this.
When someone logs in, it checks their credentials against the users table and uses users.contact_id, users.client_id, and users.affiliate_id to do a quick look up rather than having to join together a string of tables to find out the same information. But this causes duplication of data.
Without client_id in the users table, I would have to find the following information out like this:
affiliate_id: join `users`.`contact_id` to `contacts_types`.`company_id` to `affiliates`.`company_id` client_id: join `users`.`contact_id` to `contacts_types`.`company_id` to `clients`.`company_id` company_id: join `users`.`contact_id` to `contacts_types`.`company_id` to `company`.`company_id` user's name: join `users`.`contact_id` to `contacts_types`.`contact_id` to `contacts`.`contact_id` > `name`
In each case, I wouldn't necessarily know if the user even has an entry in the affiliate table or the clients table, because they likely have an entry in only one of those tables and not both.
Is it better to do these kinds of joins and thread through multiple tables to get the information I want, or is it better to have a "shortcut" field to get me the information I want?
I have a feeling that over all, this is overly complicated in some way, but I don't see how.
I'm using MySQL.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
最好进行连接。仅当您有缓慢响应的时间证据时，才应该对数据进行非规范化。
话虽如此，有多种方法可以减少输入量：
mysql 可能不支持以上所有内容 - 您需要检查文档 [更新：好的，最近的 mysql 似乎支持视图，但不支持“with”。因此您可以添加视图来完成affiliate_id、client_id等的工作，并将它们像查询中的表一样对待，但保持底层数据井井有条。]
it's better to do the joins. you should only be denormalizing your data when you have timed evidence of a slow response.
having said that, there are various ways to reduce the amount of typing:
it's possible mysql doesn't support all the above - you'll need to check the docs [update: ok, recent mysql seems to support views, but not "with". so you can add views to do the work of affiliate_id, client_id etc and treat them just like tables in your queries, but keeping the underlying data nicely organised.]