规格报告的数据库设计
我们的团队希望将我们的规格数据存储在数据库中。然而，我们在数据库设计方面需要一些帮助。
该小组决定制定一份八页的规范，其中至少有一百个单个值的“空白”。多组值以表格格式显示。如果值为空，则它不适用于给定产品。每个字段都有一个特定的数据类型。具有以下数据类型的字段均被表示：整数、小数、查找列表、文本、日期和布尔值。
数据库还应该跟踪以前的修订。
从一般角度来看，我应该如何设计我的数据库？
选项1：一张或多张一对一表
- 一张以“ID”和“Rev”为主键的大表。
- 有效修订版是具有最新“批准日期”的修订版。
- 如果字段数量超过 DBMS 限制，请将表分解为多个“一对一”字段，每个字段以“ID”和“Rev”作为主键。
选项 2：实体值
- 主表（ID、Rev）
- 对于每种数据类型：
选项？：您提出的任何想法...
注意：如果适用，我计划在整个项目（表单、表格、查询和报告）中使用 MS-Access。我们还有 SQL Server 和 MSDN 订阅，因此如果您强烈建议，这也是一个选择。
编辑：
- 这将是一个内部数据库。
- 我们有大约 150 个规范，我预计每个规范每年都会有一个新版本。
- 当问题出现时，我很可能会维护这个数据库。
- 大约有五个人应该能够创建和更新规范和修订。
- 大约二十五名员工将从该数据库检索数据（打印报告、查找规范等）
EDIT2：（HansUp 的说明）
- 我将重新考虑使用查找字段并考虑其他选项（创建另一个表，文本值等）
- 通过“每个字段将具有特定的数据类型”，我的意思是每个字段（或“空白”）将具有相应的数据类型。例如，MAX_CHAMBER_PSI 将始终是整数值，PERFORM_TEMPERATURE_TEST 将始终是 Yes/No，REVISION_REASON 将始终是字符串。
- 用户数量只是一个近似值。大约五个用户将拥有创建/更新访问权限，另外大约二十五个用户将只能读取数据。在 form_load 上，我只是计划隐藏控件并根据登录的用户更新属性。
EDIT3: 摘要：
我们设计了一份规格报告，其中包含大约 100 个字段，所有字段都依赖于 SpecID 和 Rev 来定义可接受的范围了解我们产品的各种属性。
目前，规格是手写在报告上并存放在文件柜中。此外，每个测试台的活页夹中都提供了每个规范最新版本的副本。
我应该如何为具有大约一百个属性的主键建模，这超出了 Microsoft Access 表中列的限制？
Our group would like to store our specification data in a database. However, we need some assistance in the database design.
The group decided on an eight-page specification which has "blanks" for at least a hundred single values. Several sets of values are displayed in a tabular format. If a value is blank, it does not apply for the given product. Each field will have a particular data-type. Fields with the following data types are all represented: integers, decimals, lookup list, text, date, and boolean.
The database should also keep track of previous revisions.
From a general perspective, how should I design my database?
Option 1: One or many one-to-one tables
- One big table with "ID" and "Rev" as a primary key.
- Active revision as revision with the latest "approved by" date.
- If number of fields exceeds DBMS limit, break up the table into several "one-to-one" fields each with "ID" and "Rev" as the primary key.
Option 2: Entity-value
- A main table (ID, Rev)
- For each data type:
<data_type>_category table describing each field of that data type<data_type>_value table (ID, Rev, category, value) for each instance of a value
Option ?: Any idea you come up with...
Note: If applicable, I plan to use MS-Access for the entire project (Forms, tables, queries, and reports). We also have SQL Server and an MSDN subscription, so if you strongly suggest, that is an option too.
EDIT:
- This will be an in-house database.
- We have about 150 specifications, and I expect about one new revision per specification per year.
- I will most likely maintain this database as issues arise.
- Approximately five people should be able to create and update specs and revisions.
- Approximately twenty-five employees will retreive data from this database (print reports, lookup specs, etc.)
EDIT2: (Clarifications for HansUp)
- I will reconsider using Lookup Fields and consider other options (creating another table, text values, etc)
- By "Each field will have a particular data-type," I meant that each field (or "blank") will have a corresponding data-type. For example, MAX_CHAMBER_PSI will always be an integer value, PERFORM_TEMPERATURE_TEST will always be a Yes/No, and REVISION_REASON will always be a string.
- The number of users is just an approximation. About five users will have create/update access, and about twenty-five additional users will only be able to read the data. On form_load, I just planned to hide controls and update properties based on the user logged in.
EDIT3: Summary:
We designed a spec report with ~100 fields all dependent on SpecID and Rev to define the acceptable range for various attributes of our products.
Right now, the specifications are written by hand on the report and stored in a filing cabinet. Also, a copy of the latest revision of each spec is provided in a binder at each test stand.
How should I model a primary key with about a hundred attributes, which exceeds the limit of for columns in a Microsoft Access table?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
根据我尝试以“简单”方式做此类事情的经验，我会推荐实体值方法（设计平面数据结构很容易，但处理数据却不那么容易）。
查看Duane Hookum 的 At Your Survey 数据库模板。我自己没有使用过，但我看到过详细讨论，而且他似乎使用了这种方法。
I would recommend the entity value approach, based on my experience trying to do this kind of thing the "simple" way (it's easy to design a flat data structure, but not nearly as easy to work with the data).
Have a look at Duane Hookum's At Your Survey database template. I've not used it myself, but I've seen it discussed at length, and it seems he uses that approach.
我的建议是选择你的选项#1。 #2 将会复杂一个数量级，并且对于您的情况来说可能没有必要。如果您让每个用户定义自己的字段，#2 可能是合理的，但我没有从您的描述中得到这一点。
您的规范只是一个包含 100 个字段的实体。将其建模为包含 100 个“现场对象”的“容器”实在是太过分了。
My advice would be do go with your option #1. #2 is going to be an order of magnitude more complicated, and it's probably unnecessary for your situation. #2 might be justified if you let each user define his own fields, but I don't get that from your description.
Your specification is simply an entity with 100 fields. Modeling it as a "container" of 100 "field objects" is way overkill.