工作指令的数据库布局 [英] Database Layout for Work Instructions
问题描述
大家好，
我正在构建工作指令数据库，以规范所有工作指令相关文档。目前，有许多文档具有不同的布局。我想将其减少到一种标准格式。
我已经开始使用我的数据库，但在此过程中我正在重新考虑更改数据库的（技术）布局。在继续之前，我很乐意接受一些专家意见。这是我已经拥有的部分：工作指令数据库v5.0.accdb .ZIP 。对于那些没有Access可用的人，我会尝试尽可能多地解释。
首先，我要解释我使用的定义：
工作指示
工作指令是一份文件/报告，其中包括：
Hello all,
I''m currently constructing a Work Instructions database to normalize all the Work Instructions related documents. Currently there are numerous documents with different layouts. I want to reduce that to one standard format.
I''ve already made a start on my database but during the process I''m reconsidering to change the (technical) layout of my database. Before continuing I would love to recieve some expert opinions. This is the part I already have: Work Instruction Database v5.0.accdb.zip. For those who don''t have Access available I''ll try to explain as much as possible.
First of all I''m going to explain the definitions I use:
Work Instructions
A Work Instruction is a Document/Report which consists of:
- 首页
- 安全表
- 进入质量检查
- 多个工作步骤
- 最终质量检查
- 常见故障列表
1。首页
首页是根据工作步骤中的数据自动生成/更新的。
此页面显示：
- 编辑名称
- 修订号（来自工作说明）
- 修订号每个工作步骤
- 文档标题
- 部门名称
- Opeartion名称（例如：焊接）
- 操作编号
- DepartmentID
- 步骤名称
- 模型类型
- 变体类型
- 操作
- 执行操作的图片
- 操作的英文描述
- 荷兰行动描述
- 使用过的小零件的零件数（例如螺栓，螺母等）
- 使用的小零件数量
- 每个小零件的小图片
- 每个步骤的修订版号
- 最多显示5个用于显示所用工具的图标
- 使用过的大型零件/组件的零件数量（例如发动机，车轮等）
- 所用大件的数量
- 每个大部件/组件的小图片
这是我为创建工作步骤制作的表格：
CreateStep.jpg
- DepartmentID
- Step Name
- Model Type
- Variant Type
- Operation
- A picture of the performed action
- English description of the action
- Dutch description of the action
- Partnumbers of used Small Parts (e.g. bolts,nuts etc.)
- Quantity of the Small parts used
- Small Picture of each used Small part
- Revision number for each step
- A max of 5 Icons which display the used Tools
- Partnumbers of used Big Parts/Components (e.g. Engine, wheel etc.)
- Quantity of the Big parts used
- Small Picture of each used Big part/Component
Here is the Form I made for Creating a Work Step:
CreateStep.jpg
- 编辑WorkStep / Safety Sheet / Entry Check / Final Check后，必须保存旧版本。
- 每次编辑文档时，都会出现首字母缩写编辑器和编辑日期的保存。每次保存时，修订号也会增加。
- 用户必须能够：创建，编辑和查看每种类型的文件（步骤，说明，安全表等）
- 用户可以添加，编辑和查看部件的表单
推荐答案
Jeroen，
老实说，你的问题是一种广泛的，几乎太宽泛，无法提供详细，诚实的回应。但是，我理解您的担忧，因为从长远来看第一次错误地构建数据库会产生无数的麻烦。所以，虽然广泛，你的问题对于这个论坛是有效的（我认为）。
我看到的第一件事是tblSteps中的一小部分冗余。您列出了OperationID和DeptID。但是，如果我理解tblOperations和tblDeptartment（有些人会根据你的命名约定认为这个表应该是名称tblDepartment s - 复数）正确地说，一旦你有了OperationID，你就会知道DeptID。因此，可以从tblSteps中删除DeptID。如果用户在使用表单时需要知道部门，则可以轻松加入查询（首选）以支持表单或在控件中快速查找。
第二个观察是Icon1-5。如果你有超过5个图标怎么办？这可能很少见，但没有任何意外情况。如果图标少于5个怎么办？那你浪费了空间。现在，如果总有5个图标和 仅 5个图标 - 没有失败 - 那么这种结构可以工作，可能是管理它的最佳方式。更好的方法可能是使用具有StepID的外键的表（tblIcons）来允许合并更多的更少的图标。根据您在表单中使用这些图标的方式可能会产生挑战，因此，请根据您希望表单的复杂程度来衡量您想要做什么。
此外，我认为表tblSteps应具有StepNumber的字段。因此，如果您的流程需要13个步骤，则可以适当地订购这些步骤。现在，我没有看到这样做的方法（除非我遗漏了什么）。
最后，我不确定我是否完全理解表的目的tblStepParts和tblStepComponents。看起来PartID和Quantity都是tblComponents中的捕获。但是，再次，我可能会遗漏一些明显的东西。
关于您编辑步骤的愿望，保留以前的版本，我认为这样做的一个好方法是使用主步骤表格显示 当前 步骤是什么。然后，当用户点击编辑步骤时，从该表单中弹出一个新表单（保留原始位置在当前步骤中。此新表单将根据当前步骤的步骤ID进行解除绑定和填充。用户的首字母将自动填充，以及当用户进行更改时，如果他们保存记录，它会将当前数据附加到步骤表中的新记录。
我认为讨论也是有保证的如何查看最近修订的步骤作为当前步骤。如果您有修订号（或修改步骤的日期），也许您填写步骤表单的查询可以将Max（）函数合并到只提取每个StepNumber的最新日期（参见上面的StepNumber）。
我希望这些反馈与你所寻找的一致。祝你的项目好运，我们'如果可以，我们会进一步提供帮助。
Jeroen,
Honestly, your question is kind of broad, almost too broad to provide a detailed, honest response. However, I understand your concerns, as building a DB incorrectly the first time can create countless headaches in the long run. So, while broad, your question is valid for this forum (I think).
The first thing I see is a small bit of redundancy in tblSteps. You list OperationID and DeptID. However, if I understand tblOperations and tblDeptartment (some would argue based on your naming convention that this Table should be names tblDepartments--plural) correctly, once you have the OperationID, you will know the DeptID. So, DeptID could be removed from the tblSteps. If the User needs to know the Department while using the Form, it is an easy join in the query (preferred) to support the Form or as a quick lookup in a control.
The second observation is Icon1-5. What happens if you have more than 5 icons? It could be rare, but there is no contingency for it. What if there are fewer than 5 icons? Then you have wasted space. Now, if there are always 5 icons and only 5 icons--without fail--then this construction can work and is probably the best way to manage it. A better way may be to have a Table (tblIcons) with a Foreign Key to StepID to allow incorporation of more of fewer icons. Depending on how you use these icons in your Forms could create challenges, though, so weigh what you want to do with how complex you want your Form to be.
Additionally, I think the Table tblSteps should have a field for "StepNumber". Thus, if you have a Process that takes 13 Steps, you can order those Steps appropriately. Right now, I don''t see a method for doing this (unless I am missing something).
Finally, I''m not sure I fully understand the purpose of the Tables tblStepParts and tblStepComponents. It looks like the PartID and Quantity are all captures in tblComponents. But, again, I may be missing something obvious.
Concerning your desire to edit Steps, retaining previous versions, I do think a good way to do this is to have your Main Step Form showing what the current Step is. Then, when the User click "Edit Step" from that Form, a new Form pops up (retaining your original location in the current Step. This new Form would be unbound and populate based on the Step ID of the current Step. The User''s initials would automatically populate, as well as the incremented revision number. As the user makes changes, if they save the record, it appends the current data to a new record in the Steps Table.
I think discussion is also warranted on how to view the most recently revised Steps as the "current Step". If you have a revision number (or a Date on which the Step was revised), perhaps your Query to populate your Steps Form could incorporate the Max() function to pull only the latest dates for each StepNumber (see above on StepNumber).
I hope this feedback is along the lines of what you were looking for. Good luck on your project and we''ll assist further if we can.
Hello Twinny，
tblDepartment s确实是tpartlDepartmens，这是一个错字。你是正确的，如果你有OperationID，你也知道DeptID。我会从tblSteps中删除DeptID。
关于5个图标：我使用过它们，因为在我看来它是最简单的方法。每个步骤最多有5个图标，但数据库将包含空格。
我创建了一个tblStepParts和tblComponents来填充我的2个连续子表单。在1号（tblStepParts已连接）子用户可以选择小部件，如螺栓等。
第二个子表单用于更大的部件，如引擎。如果将记录加载到表单上，我希望在EditStep表单的中心显示小部件（图片，数量和部件号）。较大的组件（图片等）必须显示在表单的右侧。所以我介绍这些表只是为了在我的表单上进行视觉定位。
我认为在表格中添加一个StepNumber tblSteps是不必要的。如果用户想要创建工作指令，他会按照他想要的顺序从列表中选择StepNames。如果我按StepNumber对步骤进行排序，我将无法改变工作流程。例如：如果当前的工作过程是A，B，C，我就不能将顺序改为A，C，B。
我真的喜欢打开第二张（未绑定）表单的想法。在我看来，这是一个简单而强大的解决方案，没有严格的VBA编程。唯一的困难可能是设置连续子表单。我还没有想到如何实现这一点。
根据修订版号选择最新步骤的想法已经在我的脑后。这应该会严格限制用户的选择列表。
感谢您提供有用的反馈！
Hello Twinny,
The tblDepartment should indeed be tblDepartmens, it''s a typo. You are correct on the fact that if you have the OperationID, you also know the DeptID. I''ll remove DeptID from tblSteps.
Regarding the 5 Icons: I''ve used them because it seemed to me to be the easiest way. There is a max of 5 Icons for each Step but It''s true that the database will contain empty spaces.
I''ve created a tblStepParts and tblComponents to populate my 2 continuous subforms. On the 1st (tblStepParts connected) subfrom the user can select small Parts like bolts etc.
The second subform is for bigger parts like an engine. If load a record onto the form I want the small parts (picture, qty and partnumber) displayed on the center of my EditStep form. The bigger components (pictures etc.) have to be displayed at the right side of my form. So I introduced these tables only for visual positioning on my form.
Adding a StepNumber to the table tblSteps is unnecessary, I think. If the user wants to create a Work Instruction, he selects the StepNames from a List in the order he wants. If I would sort the Steps on StepNumber I won''t be able to change the work proces. For instance: if the current work proces is A, B, C I can''t change the order to A,C,B.
I really do like the idea of opening a second (unbound) Form. It seems to me an easy and robust solution without dificult VBA programming. The only difficulty might be setting the continuous subforms. I don''t have a idea yet how I could realise that.
The idea of selecting the latest steps based on Revision number was already in the back of my head. This should limit the selection list for the User by a lot.
Thanks for providing helpfull feedback!
在表格中添加StepNumber tblSteps是不必要的， 我认为。如果用户想要创建工作指令，他会按照他想要的顺序从列表中选择StepNames。如果我按StepNumber对步骤进行排序，我将无法改变工作流程。例如：如果当前的工作过程是A，B，C，我就不能将顺序改为A，C，B。
Adding a StepNumber to the table tblSteps is unnecessary, I think. If the user wants to create a Work Instruction, he selects the StepNames from a List in the order he wants. If I would sort the Steps on StepNumber I won''t be able to change the work proces. For instance: if the current work proces is A, B, C I can''t change the order to A,C,B.
这正是我所说的。没有任何迹象表明（据我所见 - 我可能仍会遗漏某些东西），其中的步骤是A，B或C.另外，如果你有一个SortOrder，或者StepNumber，这些 始终 可以通过编程方式进行调整。例如，仅包括向上按钮。和向下按钮来调整顺序。但是，我不想对你的项目强加一个想法。
This is exactly what I am talking about. There is nothing indicating (as far as I can see--I might still be missing something) which steps are A, B or C. Plus, if you have a "SortOrder" or "StepNumber", these can always be adjusted programmatically. For example, just including "Up" and "Down" buttons to adjust the order. However, I don''t want to force an idea onto your project.
我真的很喜欢这个想法打开第二张（未绑定）表格。在我看来，这是一个简单而强大的解决方案，没有严格的VBA编程。唯一的困难可能是设置连续子表单。我还没有想到如何实现这一点。
I really do like the idea of opening a second (unbound) Form. It seems to me an easy and robust solution without dificult VBA programming. The only difficulty might be setting the continuous subforms. I don''t have a idea yet how I could realise that.
我认为第二个未绑定的表单与原始表单的构建方式大致相同。所有子表单仍然基于StepID。
这也引入了我脑后的东西....
如果StepID是表的唯一（即主键），那么如何识别哪个步骤属于哪个进程？
例如，如果StepID是自动编号（不是说它不应该），那么，如果你有进程A，我们如何知道哪些步骤属于该进程？当然，我可能仍然对你如何设想你的数据库感到困惑。
但是，如果你保持StepID（应该是），那么就不应该有了指定步骤适用的过程的字段，然后指定执行步骤的顺序的字段？我只想澄清一些不清楚的细节。我知道我可能会让事情变得更加混乱和不清楚。
也许一个例子：
你有一个表tblProcesses。此表列出了步骤适用的流程。所以....
I would think the second, unbound form would be built much the same way as the original form. All the subforms would still be based on the StepID.
This also introduces something in the back of my head....
If StepID is unique (i.e. the Primary Key) for the Table, then how do you identify "which Step belongs to which Process"?
For example, if StepID is an AutoNumber (not saying that it shouldn''t be), then, if you have Process A, how do we know which Steps belong to that Process? Granted, I may still be a bit confused by how you have envisioned your DB.
However, if you maintain StepID (which it should be), then shouldn''t there be a Field designating the Process to which the Step applies, then a Field designating which order the Steps are performed? I''m just trying to clarify some of the details that are not clear. I know I am probably making things more confusing and unclear.
Perhaps an Example:
You have a Table tblProcesses. This Table lists the Processes to which the steps apply. So....
这篇关于工作指令的数据库布局的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
