关于规范化数据库的建议 [英] Advice on Normalizing Database

问题描述

我知道这是一个非常通用的标题，对不起。


本周早些时候我发布了有关使用列表框操作数据的问题，并在获得答案时我意识到在进一步讨论之前我需要重新考虑基础表格，这使我得以实现这一点。


我从头开始使用我的新数据库;阅读关于如何规范化的许多线索（甚至发现Boyce-Codd Normal Form是什么）。所以我坐下来开始写下我需要捕获的信息，然后开始尝试将它们组织到表中，并设置适当的关系。这开始很好，但现在我被卡住了。


我对这个数据库的最终目标是捕捉空中交通管制学徒（受训人员）培训的所有方面，因为他们努力完全胜任。受训者将有一名指定的培训师，他们将通过3个培训岗位（到达，协助和RFC）进行培训，每个岗位分为多个区块（模拟器，Blk1，Blk2，Blk3等），并在这些区域内他们将训练STS（特殊训练系列）项目，其中有300多项。其中一些项目是在多个位置接受培训（虽然标准不同），我需要能够在每个STS项目中捕捉他们的进展，具体到他们所处的位置和训练块。这就是我的大问题。


我建立了一个主人。列出STS编号的STS表，它的标题，然后我为每个块创建了是/否字段（AA_Blk_Sim，AA_Blk_1，AR_Blk_Sim等）。虽然这些STS项目正在接受培训，但它们将处于三种状态之一（未涵盖，培训，完成），这将与受训者联系。


因此对于每个受训者我都是需要为STS项目添加300个字段，以及为每个位置在这些字段中定义状态的字段（总共1500个字段，还是有更有效的方法来执行此操作？我坐在这里盯着将所有这些领域添加到我的实习记录的前景，我觉得必须有一个更有效的方法来做到这一点。


关于如何实现这一目标的任何想法或建议？

解决方案

很高兴看到你已经采取了各种人的建议来看待正常化。


一些问题： -

您坐下来为每位受训者分配了一名培训师。如果培训师不在度假并由另一名培训师替换，会发生什么情况。换句话说，受训者是否可以分配2名或更多培训师<？ br />

3个培训岗位的目的是什么（抵达，助理和... RFC（无论可能意味着什么））？


块的目的是什么？


从发布的最后部分开始，你似乎暗示一个受训者必须做所有（或一些）300个STS，你需要知道每个STS的标记。


这就是为什么我有点困惑在职位和意义上的重要性块。


您是否试图说模拟器块已经将40个STS和Blk1称为不同的50个STS等等？


在您之前的帖子中，您提供了一个非常好的图像，将值从1个列表框移动到另一个列表框，但此图仅供1名受训者使用吗？换句话说，这应该是表格顶部有Joe Bloggs吗？

感谢您的回复。


是的，学员可以有多个学员，我相信会通过这两个表（实习生，实习生）之间的多对多关系来处理


我会进一步打破ATC培训。它是一个规范和标准化的过程，你需要在每个控制位置接受正式训练。到达雷达的飞机进出跑道，协助通过固定电话进行协调，RFC基本上将飞机对准跑道。每个职位都有一定的天数分配给培训，以及培训的具体任务。 （每个100多个任务）


在每个位置，它们被分成块，这些块是具有已定义任务和分配的训练日的较小部分。 （每个30到40个任务）


为了使事情进一步复杂化，任务可以在多个位置进行训练，虽然标准不同（在飞行中引导飞机与RFC相比有所不同，所以它被训练了两次）


至于那个图表是的，我的目的是让Block特定的表单直观地跟踪该块中每个任务的状态（未涵盖，培训，完成）。这里的想法是让他们很容易看到他们在完成一个区块方面有多远，并且还防止教练失去。或者忘记每周必须手动将每个任务输入一个空白表格来训练许多任务中的一个。


（我们以前有更多的块，这使得我们可以在Adobe中使用无线电盒为每个任务状态设置块特定表格，但现在我已将它们合并为更大的块在可打印的表格上没有空间，因此我希望使用列表框）


所以我需要开发一个特定的每周训练表格，从任务表中抽取我建成。但是会将表单输出保存到特定的受训者。此外，我希望它能够提取诸如培训开始日期之类的数据，并计算使用天数和类似计算。我也希望培训师能够编写下周的评估报告，并将上周的任务状态填入新表格中，以便更新状态。


在我谈到这一点之前，我需要了解如何布局字段和表以支持它。 （我还没有上班，但是一旦到达那里，提供我桌子关系视图的屏幕上限会不会有帮助？）

好，我们是到达某个地方。

实习生/培训师的情况需要3个表 - 实习生，培训师和JoinTraineeTrainer，它们可以同时保存TraineeID和TrainerID，也可能是一个被分配到另一个的日期。


现在关于你所说的关于说法Vectoring你暗示它们的处理方式与Arrivals& RFU。我怀疑如果他们有不同的STS号码和略有不同的描述，生活会变得更容易。这可能吗？


我的想法是，为了覆盖STS方面，会有3个表格

<跨度类= codeLink的onclick = 模糊（此，this.parentNode.parentNode，的getChildren（本），TRUE）;>展开 <跨度类= codeDivider> | 选择 | 裹 <跨度类= codeDivider > | <跨度类= codeLink的onclick = LineNumbers（本）;>行号

I know that''s a very generic title, sorry.

I posted earlier this week with questions about using a listbox to manipulate data, and while getting answers for this I realized I needed to rethink the underlying tables prior to going any further, which led me to this.

I started from the ground up with my new database; reading numerous threads on how to normalize (even found out what the Boyce-Codd Normal Form is). So I sat down and started writing down what pieces of information I need to capture and then started trying to organize them into tables, and set the appropriate relationships. That was going fine initially but now I''m stuck.

My ultimate goal with this database is to capture all of the aspects of training for air traffic control apprentices (trainees) as they work towards being fully qualified. The trainee''s will have an assigned trainer, and they will progress through 3 positions of training (Arrival, Assist, and RFC), each broken down into numerous blocks (Simulator, Blk1, Blk2, Blk3, etc) and within those blocks they will train on STS (Special Training Series) items, of which there are over 300. Some of these items are trained in multiple positions (albeit to a different standard) and I need to be able to capture their progress in each STS item, specific to the position and block of training they are in. This is where my big hang-up is.

I built a "master" STS table that lists the STS number, it''s title, and then I created yes/no fields for each block(AA_Blk_Sim, AA_Blk_1, AR_Blk_Sim, etc). While these STS items are being trained they will be in one of three states (Not Covered, Training, Complete), which will be tied to the trainee.

So for each trainee do I need to add 300 fields for the STS items, as well as fields to define their state in aech of those fields for each position (1500 fields in total, or is there a more efficient way to do this? I''m sitting here staring at the prospect of adding all of these fields to my trainee records and I feel like there must be a more efficient way to do this.

Any thoughts or advice on how to get this accomplished?

解决方案

Glad to see you have taken various people''s advice to look at normalisation.

Some questions:-
You sat that each trainee is assigned a trainer. What happens if the trainer is away on holiday and is replaced by another trainer. In other words, can a trainee have 2 or more trainers assigned to them?

What is the purpose of the 3 positions of training(Arrival, Assist & RFC (whatever that might mean))?

What is the purpose of the Blocks?

From the last part of your posting, you seem to imply that a trainee has to do all (or some) of 300 STSs and you need to know his marks for each of the STSs.

That is why I am a bit confused at the significance of the Positions & Blocks.

Are you trying to say that the Simulator Block has say 40 of those STSs and Blk1 as a different 50 STSs, etc.?

In your earlier posting you gave a very good image of moving values from 1 list box to another, but was this diagram purely for 1 trainee? In other words should this have had Joe Bloggs on the top of the form?

Thank you for the response.

Yes, trainees could have multiple trainees, which I believe would be handled through a many to many relationship between those two tables (trainee, trainee)

I''ll break down ATC training a little further. its a regimented and standardized process where you need to be formally trained in each control position.Arrival moves planes to and from the runway in radar, assist does all coordination via landlines for that person, and RFC basically talks the plane down to the runway. Each of those positions has a set number of days allotted to training, and specific tasks that are trained in it. (100+ tasks in each)

Within each position they are broken into blocks, which are smaller sections with defined tasks and allotted training days. (30 to 40 tasks in each)

To further complicate things, a task can be trained in multiple positions, albeit to a different standard (vectoring an aircraft is somewhat different in Arrival vs RFC, so it''s trained twice)

And as to that diagram yes, my intent is to have Block specific forms that visually tracks the status for each of the tasks in that block (not covered, training, complete). The idea here is to make it easy to see how far along they are in completing a block, and also prevent the trainer from "losing" or forgetting to train one of the many tasks by having to manually enter every task into a blank form every week.

(We previously had many more blocks, which allowed us to have block specific forms in Adobe with radio boxes for each tasks status, but now that I''ve merged them into larger blocks there isn''t room for that on a printable form, hence my desire to use list boxes)

So I need to develop block specific weekly training forms that draw from a task table I built. But will save the forms output to a specific trainee. Additionally I''d like it to pull data such as the start date of training, and calculate the number of days used, and similar calculations. I''d also like the trainer to be able to write the next weeks evaluation and have it populate the task statuses from the previous week into the new form so their states can be updated.

Before I get to that point though, I need to understand how to layout the fields and tables to support that. (I''m not at work yet, but would It be helpful to provide a screen cap of the relationship view of my tables once I get there?)

Good, we''re getting somewhere.
The trainee / trainer situation needs 3 tables - Trainee, Trainer and JoinTraineeTrainer which would hold both TraineeID and TrainerID and probably a date where one was assigned to the other.

Now with regards to your remarks about say Vectoring where you imply they are handled slightly differently for Arrivals & RFU. I suspect that life would be made much easier if they had different STS numbers and slightly different descriptions. Is this possible?

My thinking is then that to cover the STS aspect, there would be 3 tables

Expand|Select|Wrap|Line Numbers

这篇关于关于规范化数据库的建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！