Access ER Data Model

[Calgary_Neil]

Hello

I have been looking for help to make sure that my data model is sound. I am new to access and have an old background (30yr) since I last played with a RDMS.

If need be I can write up some logic statements for this, but generally you must remember time (which creates the manys). i.e. An Unit has one Branch at a time, but may have many over time. Time is shown by the Change.WrkDate. The table Unit is really a place holder with the table Title holding all the proper names which change over time, so effectively it is three major tables (Unit, Title, Change), linked by smaller repetitive tables.

Some questions I have are:
1) I'm finding hard to enter data without forms because such small amount goes into each table. Is this typical?
2) I have dates in three locations (so they can repeat), but dates are important to me. Is this dangerous?
3) A lot of tables are repetitive but do show different aspects. Is there away of reducing the number of tables? (to make it clear and/or easier)

I can clarify any processes that are not clear, and will do what ever I need to get this model solid.

ER Model LineageDB.pdf
pg1

pg2

Hope the Jpg are readable

Thank you
Neil

 

[silentwolf]

Hi Neil,
I am not quite sure what you are exactly after...
Is this all about access or you are working with excel and access?
Maybe you can explain it with a small example what information you like to get out of the access database so it is easier to help.

Nice greetings

Albert

 

[Calgary_Neil]

Hello Wolf
No this is an all access affair. I call it a Lineage DB because I want to track the name changes that a military units go though over the course time (100+yrs). So given a unit, list the past units and names up to it current name. Since we have all the names, list units that make up the higher and lower formations at a given time and there locations. The other wants are similar to the last one, but use different aspects of Units. For all of these be able to support where the information came from and be able to easily find it.

A new and fourth question would be on table Unit, the unit colours are between 1 and 6 (the max I think), with different widths and usages,include tartans. Should this be spun off into a seperate table with a M-M relationship cross? I haven't because I not looking at colours, just neat to have.

Thanks for looking
Neil

 

[silentwolf]

Hi Neil,
just a quite note .. I can not see any pictures you suppose to have attached.
Can you look to get those attached.. would be easier to follow along what you are after.

 

[Calgary_Neil]

This form doesn't allow attachments, (I think) so they are URL's to a flickr account with JPG's of the ER diagram. Double click on the icon to go to the picture. I could set up a "Dropbox" with public settings but I not thrilled with that idea.

 

[Calgary_Neil]

Having just tried the above on my IPad, it dosn't work (Windows seems to), so here are the URL's https://flic.kr/p/JW8pNR and https://flic.kr/p/JVUGe3

 

[silentwolf]

Hi Neil,

thanks for uploading your dbmodel..Indeed there are many tables and I understand that this is getting a bit confusing.. I will look into it a bit closer when I get more time..

 

[silentwolf]

Hi Neil,
I could not wait .. and have more questions... regarding the model..
1. Information is supose to be stored in a unit..I guess Location, Name, Nickname, ..
2. Branch? Also locations?? I am not quiet sure what a branch is compare to a unit??

However what I have seen I would do it a bit different with the model... tblUnit.. tblUnitBranch....tblBranch... (m:n) Many to many relationship...
So therfore a unit can belong to more then one branch or vise versa.. In the tblUnitBranch you then can add fields like DateChanged or something like that

That means that when you have a many to many relationship it will have a unique ID for what ever it is say UnitA belongs to BranchA from the 1/1/2015 to 1/1/16... When UnitA belongs after to BranchB the Date Would be something like 1/1/2016 to 1/7/2016

So there is no need to more tables for change.. Just enter the dates into the tblUnitBranch.. and perhabs have serveral Lookup Tables to get ColorCode...or what ever into this table..
After that you can if you need more information regarding this UnitBranch you can have another table linked to this so it becomes a 1:n Relationship from tblUnitBranch to say tblMembers...So one UnitBranch is unique regarding on what date and what branch it belongs to.. and this UnitBranch has Members also unique... or other information...
The problem that I am seeing in your model is what many inclulding myself done at the beginning of creating a dbModel..(missing the power of queries!) So there is no need to have all different tables for a model as queries will do the filter!

P.S. I keep the dbmodel layout from 1.n so the 1 side is left and the linked tables are to the right .. just a preverence... but one important thing I guess is that you are renaming the FK(foreign keys Unique!) So something like Unit_ID_Ref or UnitIDRef,,,so that way when the model is going forward it becomes easy to know what is the PK (primary key) and what the FK...

HTH

 

[Calgary_Neil]

Just a quick reply to your can't wait...
This DB is design to cover to cover over 100 years of military history (British), which most people find confusing and leave alone.

No branches are like trades, note that branch is also a unit because its name changes over time. Units are holders, Location is done with Places, Names & nickname are in Titles, and Changes mark the When, Why and Who of it.

When you visualize this it has three main hubs Unit, Title, and Change, which makes the left and right thing hard to do.
Now to have Coffee
Neil

 

[Calgary_Neil]

Hello SilentWolf

I'm going handle your quick note in small parts. I really do appreciate you taking the time with this.

but one important thing I guess is that you are renaming the FK(foreign keys Unique!) So something like Unit_ID_Ref or UnitIDRef,,,so that way when the model is going forward it becomes easy to know what is the PK (primary key) and what the FK...

Am I to understand that PK and FK need to be different? (should, can) I felt that the reuse of Unit in the Unit table was redundant and dropped that habit. I'm surprised at my sloppiness that it's still there.
Should I just make sure that ALL field names are unique?
Do I really need FK and PK in the name when I KNOW that they are? Does it help that much?

So using SQL (Much on the web about it) rules then I should use UnitxBranch_UnitID which looks a lot like the PK for Unit_Branch. I could change ID to PK and FK giving UnitxBranch_UnitFK and something like UnitxBranchPK. Still close but ...

Thanks
Neil