Question on stars and repetitive table

[Western_Neil]

Hello
I’m lost in the forest of data and can’t see the order for the trees. I have been playing with the data to setup importing, which is causing changes to my proposed data tree. (All VERY GOOD, Thanks Orange and Neil for the ideas, readings and the thoughts).



A typical data fragment is like “A” Company, 3^rd Battalion, 502^nd Parachute Infantry Regiment, was in Bastogne Belgium on Dec 1944. This statement has Unit, Title (“A”), Size (Company), Command (3^rd Battalion), Family (502^nd Regiment), Branch (inferred Infantry), Type (Parachute Infantry). This units’ Lineage is unstated but it was mobilized as infantry and converted in 1942. This whole fragment can also be shown as a graphic.

This data wants to break down (as I understand normalization) into following statement, a Unit has a Title, Lineage, Family, Command, Branch, Type, Size, and is in a Place. All of these things occurred because an Orders had been issued. An Order has a Working date, Date Range, Raw Date, Action, and if known the Authority, Effective date and Issued date. From this I’m getting a triple star data tree (Unit, Title, and Order) with repetitive table structure (UnitID, TitleID, OrderID, Note, and type filed or two).

So my question is multiple stars and repetitive table structures normal for a good database design?

Tree, data and order sample can be posted latter

[CJ_London]

not sure what you mean by multiple stars - are you referring to a multi field primary key? If so, I would suggest have a separate autonumber primary key and a multi field index on your three fields to prevent duplicates. To do this, in the table design window, click on indexes in the ribbon, enter an index name and select unique at the bottom, then the three field names and change sort order if required - like this



Also not clear about repetitive table structures. If you mean you have a number of different tables for lookup purposes which all have the same structure of a PK and a description, but one relates to unit, another to place etc that is OK. It is also OK to combine them into one table with an extra 'type' column which indicates what was previously the table name. The first is easier to manage, the second more flexible if there is the likelihood of additional 'types'.

[Western_Neil]

By star I mean a table joined to many tables, so when drawen create a star

[CJ_London]

Ah OK - no a star is fine, but of course subject to the requirements the db needs to fulfil. From what you are saying, your Orders table is the centre of that star with links to many other tables to effectively populate a sentence/paragraph

[Western_Neil]

Correct, with title and unit doing the same thing around them selfs. It makes for a messy diagram.

Combining the other tables into a super table has always been wrong to me, but that would clean up the messy diagram.

Thoughts

[CJ_London]

but that would clean up the messy diagram.

not really - to show in the relationships you would bring the table in under different aliases - access will append _1, _2 to the table name - because you can only show one relationship between two tables



this shows two tables - and myTable being there twice with the second iteration suffixed with _1

[Western_Neil]

Okay, Thanks. Use aliases tables to clean it up. I just wish you could force access relationship lines into straight lines with 90^ Conners.

Thanks for confirming that this sounds correct.