Repeating Titles

[Western_Neil]

I am still kicking the "can" around the block. I haven’t solved my "Single Field" problem yet, but I now have a different question, but linked.




I am making a database of military units and their relationships. In WW I the British created many Artillery "Brigades" and instead of numbering the "Batteries", used letters A though D on them (after 550+ Bateries).
e.g.
9th Bde., R.F.A. ​ having 19 20 28 D/69 Batteries
26th Bde., R.F.A. having A 116 117 Batteries
189th Bde., R.F.A. having 84 B C D Batteries
242nd Bde., R.F.A. having A B C D Batteries


Now in reality all 15 "Batteries" are separate items, giving 19 different unit records.


Since so much of the titles repeats, I have a separate Tables for Titles (9th), Size (Bde), and Branch (R.F.A) and others. Now the last two also refer to Titles to allow the use of Abbv, Std or Full names (abbv shown).


So, for a given date, 1 unit record would be associated with Title, Size, Branch, and some Sub-Units, and some Headquarters and other tables. In the above example the two "C Btys" would be associated with the same records, except for the Headquarters ones. Note the "D/69" Bty which transfer from the 69 Bde Sep 1917.


On the Western Front, 11 Nov 1918 there were 115 "A Btys RFA". Add in the 600+ "A Coy" in the Infantry Battalions and this doesn't sound normalized to me, but they are distinct "Batteries" & "Companies". This means to me 700+ unit records with 2 Title records.


So, the question is Would YOU use a generic "A Bty" Title record or am I over normalizing?
If using a generic Title record, should it be marked as such? (within the record) If so after how many uses (typically)?


Neil


Yes I know "it depends...". Looking for common problems and traps on this approach.


PS the "Single Field" table is the Unit record, since over time I have found occurrences of each table relation changing (1 out of hundreds).

[CJ_London]

normalisation is not about avoiding repeating text (A Battery), its about identifying a defined unit/object/whatever. This sentence seems incomplete ' Note the "D/69" Bty which transfer from the 69 Bde Sep 1917.' transferred to where? but on the basis a battery is the smallest unit in this particular universe and can be moved to be 'owned' by another brigade, perhaps in the same branch, perhaps another, then that would seem to be as far as you need to go. But is that the case? or is it a case that a battery is disbanded in one brigade and reformed in another with different personnel/abilities etc?

This statement also seems incomplete 'On the Western Front, 11 Nov 1918 there were 115 "A Btys RFA".' Presumably that implies there were at least 115 brigades? You will have 115 unique primary keys to identify each individual A battery. In the commercial world it is perfectly acceptable to have two or more customers with the same name - because they are uniquely identified by the primary key

[Western_Neil]

normalisation is not about avoiding repeating text (A Battery), its about identifying a defined unit/object/whatever.

Thanks, it just seems weird to see so many.

This sentence seems incomplete ' Note the "D/69" Bty which transfer from the 69 Bde Sep 1917.' transferred to where?

To the 9th Bde, RFA

but on the basis a battery is the smallest unit in this particular universe and can be moved to be 'owned' by another brigade, perhaps in the same branch, perhaps another, then that would seem to be as far as you need to go. But is that the case?

Yes that is correct. I can go smaller (Troop/Platoon) but there are more of an internal organization thing.

or is it a case that a battery is disbanded in one brigade and reformed in another with different personnel/abilities etc?

That is called reflagging and since the 60's America have done it more and more. Basically one unit ends and the another unit is re-established. (same location, personnel, equipment, different hertiage)

This statement also seems incomplete 'On the Western Front, 11 Nov 1918 there were 115 "A Btys RFA".' Presumably that implies there were at least 115 brigades? You will have 115 unique primary keys to identify each individual A battery. In the commercial world it is perfectly acceptable to have two or more customers with the same name - because they are uniquely identified by the primary key

Again that is totally correct.

So, the question is still would YOU use a generic "A Bty" Title record or am I over normalizing? (assuming Yes now to using a generic Title record)
If using a generic Title record, should it be marked as such? (within the record) If so after how many uses (typically)?

Thanks for the reply

[CJ_London]

So, the question is still would YOU use a generic "A Bty" Title record

As you've explained it, No, I wouldn't.

The only time I would is if it was a business rule requirement e.g:

• if 'A Bty' meant the same everywhere in some way - they carry out a specific task, have the same structure in terms of personnel and equipment etc. and you are interested in this detail.

• you mentioned troop/platoons - could these be structured differently under different Bty's, A or otherwise? If so and it is possible you will want to go down to this level some time in the future then by all means use a generic A Bty title record because that is what you would attach that level of detail to.

• you wanted to regularise spelling to ensure users cannot misspell 'A', 'B' which I think is unlikely on your case unless you are regularising the use of Bty/Battery etc - but this in itself is not normalisation - it is more a lookup table rather than a title table.

[ArviLaanemets]

1. You need a table, where all military units your database follows are registered (batteries included). For best, use an autonumeric ID to identify every unit.
tblMilitaryUnits: MUID, Title, Size, Branch, ...
2. Units have an identificator used by military. As follows from your example with "D" > "69", those can change. So you need another table
tblMilitaryUnitCodes: MUCID, MUID, CodeDate, Code
3. Now you need a table, where you register, to which military unit in next hierarchy level at which time every military unit belonged to.
tblParentMilitaryUnits: PMUID, MUID, ParentMUID, DateFrom, ...

[Western_Neil]

Thanks Ajax

• you mentioned troop/platoons - could these be structured differently under different Bty's, A or otherwise? If so and it is possible you will want to go down to this level some time in the future then by all means use a generic A Bty title record because that is what you would attach that level of detail to.

Historical information on those sub-units are rare, but the typical is common. There is some reporting at Company/Battery level but mostly at the Battalion/Brigade level or higher. Major equipment is easier to track but is still near rare. I haven't figured out how I'm tracking official manpower/equipment/organization (hard) and actual (rare).

• you wanted to regularise spelling to ensure users cannot misspell 'A', 'B' which I think is unlikely on your case unless you are regularising the use of Bty/Battery etc - but this in itself is not normalisation - it is more a lookup table rather than a title table.

Yes one aim is to regularising the use of Bty/Battery. Thank you I think you just pointed out a model flaw.

So the question still is (or did I miss something) does tblTitles need a field Generic (used 10's out of 10,000's)?
and if so, after how many uses (typically) should this marked? I have units that change their name by dropping and then adding "The", doesn't make the name Generic. I also have lots of "1" (Generic) but is "1066" ?

The current model looks like this:

Tables not shown (part of)

tblUnitForces
tblUnitServices
tblUnitTiers
tblUnitNations
tblUnitSymbols
tblUnitLocations
tblUnitStaffs
tblUnitHonours
tblUnitHeritages
lkpAction
lkpAccuracy
tblOrders

tblUnits	UnitID						Note Single field table
tblUnitTitles	UnitTitleID	UnitFK	TitleFK	FrChangeFK	ToChangeFK
tblUnitBranchs	UnitBranchID	UnitFK	BranchFK	FrChangeFK	ToChangeFK
tblUnitSizes	UnitSizeID	UnitFK	SizeFK	FrChangeFK	ToChangeFK
tblUnitCommands	UnitCommandID	UpperUnitFK	LowerUnitFK	CommandFK	FrChangeFK	ToChangeFK
tblChanges	ChangeID	OrderFK	WorkDate	ActionLK	AccuracyLK	ActualDate	Note
lkpCommand	CommanID	Command	Description
lkpSize	SizeID	Size	S_Abbv	Graphic	HexCode	Font
lnkBranch	BranchID	B_Desc					possible single field
tblBranchTitles	BranchTitleID	BranchFK	TitleFK
tblTitles	TitleID	Title	Abbv	Short	Full	Formal

[CJ_London]

So the question still is (or did I miss something) does tblTitles need a field Generic (used 10's out of 10,000's)?

I've told you what I would do (not use a generic field), I don't believe I can add anything else. You know your subject better than anyone else, so it is your decision. You would still have the same number of records because if you went with a generic field, you would still need records to reference it

[Western_Neil]

Thanks Ajax, I missed that. Yea that makes sense in that it cuts out a bunch of rules.

And Thanks Again Ajax for working this problem though for me.

I have two other problems (single field and Referential Integrity) threads ?Unable to Enforce Referential Integrity

[CJ_London]

the unable to enforce message will be because your existing data does not fit the rules - basically you have orphan child records

to find them, create a query and left join the child table to the parent table on the relevant field. then set a criteria to any of the fields in the parent table to is null.

Then either delete them or create the relevant parent record

Not sure what you mean by single field

[Western_Neil]

Okay, I will start a new thread

Also see https://www.accessforums.net/showthr...le+Field+Table thread