473,287 Members | 1,560 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Advice on Normalizing Database

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?
May 29 '16 #1
11 1171
PhilOfWalton
1,430 Expert 1GB
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?
May 29 '16 #2
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?)
May 29 '16 #3
PhilOfWalton
1,430 Expert 1GB
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
  1. TblSTS
  2.    STSID
  3.    STSDEsc
  4.    BlockID      FK
  5.  
So for example, the Simulator Block would show all the relevant STSs

Expand|Select|Wrap|Line Numbers
  1. TblBlocks
  2.    BlockID
  3.    BlockDesc
  4. PositionID    FK
  5.  
  6.  
May 29 '16 #4
NeoPa
32,554 Expert Mod 16PB
I won't get too heavily involved here, but I can't leave such a question without a link to Database Normalisation and Table Structures.

It's been such a help to so many people, and I count myself in that group.
May 30 '16 #5
NeoPa, thank you for the response and for the link, that was actually one of the posts that made me decide to rework my database.

PhilofWalton, thank you as well. I'll build the trainee/trainer combined table, but my question is, what is the difference between that and a many to many join relationship in terms of functionality?

As far as the STS items being trained in multiple positions, they have a set number for each one. And I worry that if information changes for that STS item (title, references) that it would result in inconsistencies in the database. Here is what I've done thus far with the layout of that table. Can you try to explain why this is inadequate for what I'm trying to accomplish?

Also, the STS numbers are unique to the ID, is there any reason not to use that as my key field in that table as opposed to the longinteger number that defaults to field1 in all tables?

Based upon the screenshot I've provided, and understanding now that two pieces of information shouldn't reside in a single field, is it a violation of normalization to have a single field for AA_Blk1, AA_Blk2, AA_Blk3, or should there be an AA field, and then fields secondary to that for Blk1, Blk2, Blk3?
Attached Files
File Type: pdf tbl_STS.pdf (192.6 KB, 188 views)
File Type: pdf ATC_relationships.pdf (55.7 KB, 160 views)
May 30 '16 #6
PhilOfWalton
1,430 Expert 1GB
Sorry that my last contribution wasn't finished, I hit some unknown combination of keys and the message got posted.

I will finish that of first, then come to the information in your last post.

I intended to say that TblBlocks tells you what Blocks need to be done at each position.

Finally you need a table of Positions
Expand|Select|Wrap|Line Numbers
  1. TblPositions
  2.     PositionID
  3.     Position
  4.  
Now lets come to your current and very helpful post.
Firstly in your relationship diagram the TblPositionTraining is in fact the join table I mentioned, but you are missing the relationship with that table and the TblTrainer.

I,m sorry to say, I don't like the look of your tables... you may have a reason, but I'd like to know what it is.
The tables Trainer & Trainee both seem to have a spare field ID. The CACID should be an autonumber and as you have got it the Primary key.
The TblPositionTraining doesn't need the ID field, but the CACIDTrainer and the CACIDTrainee should be a combined key.

IMO you will find life much easier if field names in different tables have different names. E.G. In TblTrainers call the Last "TrainerLast" and in the TblTrainees, "TraineeLast". The reason being that when you start to write queries, you will get error messages like "which 'Last' do you mean?"

Back to your concerns about my suggestion that the same STS used in different blocks should have a slightly different number. One way round this is to have 2 identical STSs, bearing in mind that there should be an STSID autonumber as the key, so they would have a different ID. To differentiate between them (bearing in mind that the STSs will only be used at the setting up stage) you have a very subtle difference between then like a full stop at the end of the description for one, and not for the other.

Phil
May 30 '16 #7
jforbes
1,107 Expert 1GB
I'm not so sure Trainer and a Trainee should be separate tables. I would think an Employee table would work as an Employee could act as both trainer and trainee. A Trainee is just an Employee that has taken classes. Similarly, a Trainer is just an Employee that has Trained another Employee. I would think one Employee becomes another's Trainer when they train them on something.

I would also think that an Employee could have a Default Trainer field so that when they are Trained, the Default Trainer would be associated with the training and could be overridden if needed.

I would go with something like this:
tblEmployee
  • EmployeeID (PrimaryKey)
  • Name
  • Rank
  • DefaultTrainerID
  • ...
tblSTS
  • STSID (PrimaryKey)
  • Title
  • ...
tblEmployeeTraining
  • EmployeeTrainingID (PrimaryKey)
  • EmployeeID
  • STSID
  • TrainerID
  • StartDate
  • EndDate
  • Complete

With the above, you would enter in all your tblEmployees and tblSTS records. Assign a Trainer to an Employee as needed by setting the Empoyee's DefaultTraineerID to that of their Trainer. Then as an Employee is being trained on an STS, create an entry into tblEmployeeTraining linking the Employee to the class they are taking. While doing so, copy the tblEmployee.DefaultTrainerID into tblEmployeeTraining.TrainerID so that there is a record of who did the training for that Employee/STS combination.
May 31 '16 #8
PhilOfWalton
1,430 Expert 1GB
I've got no major problem with that.
The interesting question is does the combination of
EmployeeID
STSID
TrainerID
need to be a unique key? If So the EmployeeTrainingID is redundant.
What happens if the trainer gets changed before that particular STS is completed?
One also needs to ensure the EmployeeID <> TrainerID

Phil
May 31 '16 #9
Thanks so much for the responses guys, I haven't given up on this. Unfortunately it's a pet project of mine outside of my primary responsibilities.

The responses have been extremely helpful, and I'm slowly making progress.

The reason I had trainee and trainer separated is that we are effectively like a school or college when it comes to training. We get the trainee's fully trained and then send them on their way, whereas the trainer's are here on a permanent full time basis.

I dropped the ID key in many of those tables, I wasn't sure at first but your objections confirmed my suspicion that the CACID would work the same way (they will always be unique as it's a number on our ID cards that no one else will have)

I also appreciate the feedback on multiple STS items. I'm clearly a novice at this, and I do better by understanding than I do through just blindly following instruction (although I know it is necessary and helpful to do so at times). With that said, in reading about normalization I thought some of the intent is to avoid duplication of data, and also to prevent corruption of data by having a piece of data getting changed and not getting updated where it's used elsewhere. If I make multiple entries for the same STS item wouldn't that open myself up to getting this type of data corruption? Is there a clear reason why using fields for each of the blocks with true/false boxes is not an ideal solution for these? It seems pretty simple (in my mind)to simply add an if/then statement in the query when I'm populating the forms to ask if it's true for that item and if so to include it in my forms, followed by a second if/then based on the state specific to the trainee to determine which listbox it goes to.
Jun 3 '16 #10
PhilOfWalton
1,430 Expert 1GB
OK, I'll try to deal with your last points one by one.

The advantage of having a table to joint the Trainer to the Trainee, rather that having them all in one table, is that the join table can hold additional information such as DateAssigned and DateDeAssigned. This could be useful if you want to know how many pupils the trainer is currently responsible for. I agree with jforbes that it isn't strictly necessary, but in all my join tables there are additional fields.
For example, I have a situation where we have a number of boats (yachts) who say they want to go on a cruise in company. In the Join table, the Combined Key is CruiseID & BoatID. The additional information is number of crew, relevant notes and whether they actually went on that cruise.

Having dropped your ID fields, make sure that your CACID is set as the key, required, Indexed(No Duplicates)

There is, without doubt a problem with the STSs and duplication. My gut feeling is that if a particular STS is handled differently in any way for say Arrival & RFC, then it's a different STS. The wording and STS number may be the same, that's not a major problem.
How often does the same STS get used in different locations?

I will come back to this in a momement.

The reason I don't like the design of the tbl_STS than you illustrated is that there is no flexibility. If, in the future, another block is added (and you have already remarked that the number of blocks has been reduced) all sorts of things have to be redesigned. The flexibility of the design I am suggesting results in adding records, rather than fields as in your design.

Back to the STS problem.
Let's assume you have 2 identical STSs with STSIDs of 5 and 10, the former used in the Arrival training and the latter in the RFC training. So suppose we want to change something on STSID (5). It is very simple to check if that STS Number (5 3.1 in tour table) is used elsewhere and we might find it is used for STSID (10). At that stage you can issue warning and / or change STSID(10) to match. Ditto for the STSTitle.
In fact it may pay to have an additional field (UsedFor) in the STS table to indicate where that STS is used (Arrival, Assist and RFC). A unique combined key of STS Number and UsedFor to prevent duplication.

Phil
Jun 4 '16 #11
PhilOfWalton
1,430 Expert 1GB
Sorry, 2 more thoughts.

Firstly everyone is urging you to normalise your database...Why?
Don't forget that data is entered though forms and retrieved through forms or reports. Your design as shown in tbl_STS.PDF has 12 blocks visible (there may be more). Any form you design will also have those 12 blocks and their titles. If the number of blocks gets changed or the titles get changed, the design of the forms (and reports) also has to be changed.

With a normalised database, you will have a main form based on the Blocks table, so that the title of the form will change from Core to AA_Blk_Sim .... AR_Blk3 etc as you move along the records. On that you have a subform with all the STSs related to that block, as many or as few as are relevant.

I remarked that Join tables are useful because they can contain additional information.
At some point you will need a table to join the Trainee to the STSs. The additional information you will almost certainly want is date the trainee started and finished the course and some sort of passed/ failed or marks and and notes from the Trainer.

Phil
Jun 4 '16 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Evan Escently | last post by:
Hi, I've laid out a _very_ simple database that tracks my artwork the table 'works' looks like: +---------+----------+------------+------------+-------------+ | work_id | title | media ...
2
by: Mike D | last post by:
I am using oracle, not by choice. I am getting some errors that I can't debug. I have my db stuff in a module with public functions named database.vb Then I am trying to call the function from...
0
by: Michael Wimmer | last post by:
Hi, I need some advice on database replication. A customer wants his databases replicated between two servers, connected by the internet. The database structure will be quite simple, mainly...
4
by: Joe User | last post by:
Hi all....I have a feeling this is going to be one of those twisted query questions, but here it goes anyways.... I want to generate a report that shows the chronology of events (represented by...
2
by: Mike | last post by:
Hi I have been tasked with converting my pulp and paper mills weekly projected and actual contractor hrs excel spreadsheet into a an Access 97 database. So far my design has been to use a...
3
by: James Armstrong | last post by:
Hi all, (warning - long post ahead) I have been tasked with designing a database for my company which will store trade information (it is a financial firm). It will need to export this info...
47
by: ship | last post by:
Hi We need some advice: We are thinking of upgrading our Access database from Access 2000 to Access 2004. How stable is MS Office 2003? (particularly Access 2003). We are just a small...
29
by: MP | last post by:
Greets, context: vb6/ado/.mdb/jet 4.0 (no access)/sql beginning learner, first database, planning stages (I think the underlying question here is whether to normalize or not to normalize this...
8
by: Richard Hollenbeck | last post by:
I have a recipe database that I've been building but I haven't yet put any of the ingredients in because of a little problem of normalization. If I build a table of ingredients, all the recipes...
2
by: Martin Arvidsson | last post by:
Hi! I am going to build my firs Windows Application that involves a Database. Now this database and its tables are quite large so i got a couple of quizes regarding performance etc just to get...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.