Entering data only once after normalisation

[FMAlanbrooke]

Hi, I understand how to normalise a table but what I don't understand is how I get to enter data only once for related tables. I'm designing a military history database so I have, for instance, a unit table and a commander table. So I put a commander foreign key in the unit table and I enter all the data about the commanders in the commander table. OK so now I have a lot of information about the commanders in the commander table and in the unit table I have nothing about which unit has which commander. Does all this suppose that once you normalise the data, you create a form that is used to enter the unit data and the commander data simultaneously so that you can represent which unit has which commander (and only have to do it once)? Or do I have to look up the commander codes and enter them separately into the unit table (surely not)? There are too many commanders to use a lookup table ( I assume a lookup table with more than about 10-15 entries loses its usefulness).



I looked for an Access database design that could model a World War 2 military hierarchy but was surprised to find it unobtainable.

[pbaldy]

I'm confused (not unusual). You mentioned having a field with the commander FK in the unit table. Doesn't that give you "which unit has which commander"? On a form to enter unit data, I would expect to have a combo box bound to that FK field that got its selections from the commanders table.

[FMAlanbrooke]

I'm confused (not unusual). You mentioned having a field with the commander FK in the unit table. Doesn't that give you "which unit has which commander"? On a form to enter unit data, I would expect to have a combo box bound to that FK field that got its selections from the commanders table.

I can see that this would seem a pretty strange sort of question to someone who knows Access really well but I don't see anything happening when I put a foreign key in a table (and connect it). Thanks, does creating the form as you suggest mean that the combo box uses the commanders table as a lookup table, and you have to select them that way? What is the recommended procedure to fill out the data, to not enter any data at all until you have created the form including the combo box, or to fill out the commanders table first, then fill out the unit table via the form?

[pbaldy]

What do you expect to happen? Basically having the FK there allows you to include both tables in a query and return values from both for forms and reports.

Yes, the combo would use the commanders table as a lookup table. You can select or simply type in a value if you know it.

[FMAlanbrooke]

Thankyou! There is so much fuss in database design tutorials about the tables that you expect to see some change in the tables if the database is designed properly - clearly there should be more emphasis on designing the forms etc and their content as a means to connect the tables.

[pbaldy]

Happy to help and welcome to the site by the way!