I had previously posed a question about storing a military command hierarchy to a SQL Server database. Since then I have learned that the hierarchy could expand to include more than 3 tiers so I've decided to move the command hierarchy to an XML file. However, I still need to relate some data in my database to that command hierarchy, but I'm not exactly sure how to relate the two.
My command structure would look similar to:
In my 'Personnel' table I need to indicate where in the command structure a person falls. So, do I just add columns for each tier in the XML file?
PersonnelID int PK
Would this be sufficient or am I going down the wrong route?