MS Access 架构决策：多个表还是大量空值？
我发现这个线程在某种程度上有助于我的理解，但没有回答我的问题：
我的问题： 如果我正在创建一个旨在存储员工联系信息的架构（在 MS Access 数据库中），那么最好有一个用于电话号码的表，然后是一个用于地址的表，然后是另一个用于电子邮件地址的表，或者使用一个表来存储所有这些记录会更好，但超过一半的记录中的几个字段可能有 NULL 值？
我想将街道地址的不同元素存储到单独的字段中：地址：一个字段用于街道号码和名称，一个用于城市，一个用于州，一个用于国家/地区，一个用于邮政编码，另一个用于地址的任何其他名称（“ATTN：”或类似名称），也许还有更多； 对于电话号码：本质上一个是姓名，一个是号码； 对于电子邮件：与电话基本相同 - 姓名和号码。这会在电话号码列表中留下许多 NULL/空白值...事实上，我估计 70% 的记录可能有 5 个或更多空值，规模为 5,000 到 10,000 条记录。
我希望能够将它们显示在单独的列表中以及组合列表中，进行过滤和分组。任一结构都可以支持这一点（通过 JOINS/UNIONS 和 WHERE 子句）。就表结构的简单性而言，单个列表似乎是显而易见的 - 一个表比三个或更多表“更整洁”。
我认为，的答案应该取决于“存储”潜在数万个 NULL 值的效率与索引不同表的效率，以及花时间确保 UNION 与数据类型一致并构建各种其他方法来组合已经有些相关的数据。
我希望我已经足够清楚地表达了我的想法！我欢迎链接、答案、评论和问题。
I found this thread which helps my understanding somewhat, but does not answer my question:
SQL: Using NULL values vs. default values
My Question:
If I am creating a schema (in an MS Access Database) that is designed to store contact information for employees, would it be better to have a single table for telephone numbers, then a single table for addresses, then another single table for email addresses, OR would it be better to have a single table that stores all of these records, but might have NULL values for several of the fields in more than half of the records?
I would like to store the different elements of a street address into separate fields: For Addresses: one field for the street number and name, one filed for the city, one for the state, one for the country, one for the zip code, and also one for any other name for the address ("ATTN:" or similar), and maybe more; For Telephone Numbers: essentially one for a name and one for a number; For Emails: essentially the same as Telephone - name and number. This would leave many NULL/Blank values in the list for telephone numbers... in fact, I would estimate probably 70% of the records would have 5 or more null values, on the scale of 5,000 to 10,000 records.
I would want to be able to display them both in separate lists as well as in a combined list, filtered and grouped. Either structure could support this (through JOINS/UNIONS and WHERE clauses). In terms of simplicity of table structure, a single list would seem obvious - ONE table is "neater" than three or more tables.
The answer, I think, should hinge on the efficiency of "storing" potentially tens of thousands of NULL values vs. the efficiency of indexing different tables, and spending time ensuring UNIONs line up with datatypes and constructing various other methods to combine data that is already SOMEWHAT related.
I hope I have presented my thoughts clearly enough! I welcome links, answers, and comments as well as questions.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我会偏向于为每个实体类使用单独的表来进行设计。 Person是一个实体类。如果每个人的电话号码不超过一个，您可以将其存储为 Persons 表的属性。
然而，我通常看到的是希望能够灵活地为每个人存储多种类型的电话号码：家庭；电话号码；电话号码。工作;细胞;传真;将它们存储在单个表中（Person_ID、work_phone、home_phone、cell_phone）会导致设计脆弱。当经理告诉您为另一种电话号码类型添加字段时，您被迫修改表结构以及使用该表的查询、表单和报告。
我倾向于在 People 和 PhoneNumbers 之间建立一个具有一对多关系的单独表 --- 这样每个电话号码及其类型都是 PhoneNumbers 表中的单独行。这种设计避免了单表方法的脆弱性。它还避免了您对存储如此多 Null 值的担忧 --- 如果某个人没有电话号码，那么 PhoneNumbers 中就没有该人的行。
不过我真的不知道这个建议是否适合你的情况。我认为这取决于您的数据需求的复杂性。
至于单桌的“方便”，对我来说似乎无关紧要。访问是关系性的，因此您可以使用查询将多个表中的相关部分收集到所需数据的完整视图中……这可能类似于单个表。如果您故意避免使用这种关系功能，那么将联系信息存储在电子表格中也许不会造成太大损失。
I would approach the design with a bias favoring separate tables for each entity class. Person is an entity class. If you have no more than a single phone number for each person, you can make this work to store it as an attribute of the Persons table.
However, what I usually see is the desire for the flexibility to store multiple types of phone numbers for each person: home; work; cell; fax; etc. Storing those in a single table (Person_ID, work_phone, home_phone, cell_phone) leads to a brittle design. When the managers tell you to add a field for another phone number type, you're forced to revise the table structure, as well as queries, forms, and reports which use that table.
I would lean towards a separate table with one-to-many relationship between People and PhoneNumbers --- so that each phone number and its type is a separate row in the PhoneNumbers tables. That design avoids the brittleness of the single table approach. And it also avoids your concern over storing so many Null values --- if there is no phone number for a Person, you don't have a row for that Person in PhoneNumbers.
However I really don't know whether this suggestion is appropriate for your situation. I think it depends on the complexity of your data needs.
As for the "convenience" of a single table, that seems inconsequential to me. Access is relational, so you use a query to gather up the related pieces from multiple tables into a full view of the data you need ... which can resemble a single table. If you're deliberately avoiding that relational capability, perhaps you wouldn't lose much by storing your contact information in a spreadsheet instead.
与企业客户的跟踪信息不同，公司通常对存储员工信息有简单的要求。无需输入账单、送货或办公室地址以及各种电话号码。只是没那么复杂。
对于大多数员工来说，可能不需要 Address2 字段，但那又怎样呢？我认为一旦有人被雇用，就不需要个人电子邮件地址（将出现在简历/简历上并在面试过程中使用。）。 2-3 个电话号码应涵盖它。
我只是不确定您使用不同的表添加的复杂性是否有任何业务需求。
Unlike tracking information for business customers, companies usually have simple requirements for storing employee information. There's no need to get into billing, shipping, or office addresses and various phone numbers. It's just not that complex.
For most of your employees the Address2 field may not be needed, but so what? I don't think personal email addresses are necessary once someone is hired (Would be on CV/Resume and used during the interview process.). 2-3 phone numbers should cover it.
I'm just not sure there is any business need for the amount of complexity you'd be adding with different tables.