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,
Just to your question.. It is not necessary to change the names! But when you are dealing with code later on or relationships it is more clear when you are knowing which is which.. How you call it is up to you!! I call mine something like Unit_ID for the PK and Unit_Id_f for the FK just how I do it and I always know which is which..
But as I said it is up to you!
All names well let me explain it a bit ... All names should have a unique name for i.E. when I name my fields in tables I always know what field baelongs to what table.
So if I have a tblUnits my fields would be called Unit_Name... Unit_Adresse...and so on and for a table Branch something like Bran_Name...Bran_Adress... so on..
if I have a FK in the table Branch I call the FK Bran_Unit_Id_f so therefor I know the field is in the branch Table and is a FK to tblUnit...
Just makes it easier in the long run.. But many other just call them the same and I find it very difficult when you are looking for a code and relationship within a VBA code..

It is important to understand what the db should be doing and what information need to come out of it..
So take some time and write it down in sentences... like ... just a example as I am not still quite sure what you need...
A branch can have 1 or many units... A branch belongs to one or more units... A unit has members or so on .. If you suply some text how this should be sorted I am sure someone can get you to the right way..
At the moment I don't think it is write the way this is set up..

HTH

 

[Calgary_Neil]

Hmmm ... Branches ... How to explain ... for example let us take the Elgin Regiment:

http://www.cmp-cpm.forces.gc.ca/dhh-dhp/his/ol-lo/vol-tom-3/par1/eng-gen/31-cer-rdg-eng.asp

1866 09 14 Regimented as "25th "Elgin" Battalion of Infantry" >>> Branch is Infantry.
1900 05 08 Redesignated as "25th Elgin Regiment" >>> whole Army changed from Battalion to Regiment.
1903 08 01 made Dormant >>> Service change from Non Perament Active Militia to Reserve <!--?-->.
1904 05 17 Redesignated as "25th Regiment" >>> Title change and >>> made active again (maybe be a different Order).
1920 03 29 Redesignated as "The Elgin Regiment.
1940 05 24 Mobilized the "The Elgin Regiment, CASF" >>> new Family Member >>> see note.
1940 11 07 Redesignated as "The Elgin Regiment (Reserve) >>> All non CASF units changed.
1946 02 15 Redesignated as The Elgin Regiment, RCIC >>> Title to include Branch abbv.
1954 10 01 Converted tos "The Elgin Regiment (27th Armoured Regiment)" >>> Branch is now Armour.
1958 05 19 Redesignated as "The Elgin Regiment (RCAC)
1997 08 14 Converted to "31st Combat Engineer Regiment (The Elgin's)" >>> Branch is now Engineer.

Note: Most of this can be handled by 1 UnitID. In the 1920-36 and 40-45 it will need 2-3 additional UnitID's all related by the Family table to handle additional units. The top (Main) one holding the honours & history and the other ones holding the men and creating the history. (see I told you it was crazy).

Some of the Changes are unique to the one unit and some to a bunch but all have a Date, Order, and Action (Image a bunch of units linked by Command moving from Canada to England, 1 Order (Change), many Units)

Some dates are non specific which is why I'm using WrkDate (14-09-yyyy), RngDate (15 <+-15> and ActDate (Mid Sep). Orders will have dates if known and I worry about getting (and keeping) BDay, Sday, and EDay right (Birth, Start, End) in Unit right. Because of this I'm unsure on how to end a Unit stream which will have a Change (Disbanded) ....

So that brings up the last cross table Lineage. A unit may be an amalgamation of some units (which then end) or new and split into a couple of new units or just end. This table ties those units together. see

http://www.saskd.ca/famtree.htm

Also note the Branch Infantry has changed its' name 3 or 4 times in the above time period.

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!

This maybe the key that I'm missing, but I'm not getting it yet.

It's a lot but thanks for working though this
Neil

 

[silentwolf]

well now I got something to look into..

 

[Calgary_Neil]

[FONT=&quot]Hello again;

I have been playing around and have wondering if the ChangeID in tblTitle is needed. It should be just a list of titles, abbv and notes. I think it got placed there when I was thinking that I needed a source reference too. There is source reference though tblUnitTile. Thoughts ??

The little tables ( tblBranch, tblService, tblForce, and tblGovt ) should either
A) look like tblType (no ChangeID) and therefor have a single name that dosn't change over time
B) All need a crossover tables (giving M;M) with tblTItle with ChangeID giving flexible names. Just what I need 4 new tables.
Thoughts ??

My Current SS is about 6000 records long, but is not normalized fully and it contains about 3/4 of the information required. I have not yet got a table done in its own sheet (almost done places). What i'm asking is where should my next step be Loading the DB or creating 20 records and getting started on the querys and forms? Should I worry about the correctness of those 20 records?

Thanks for looking and
ALL comments are welcomed
Neil[/FONT]

 

[Calgary_Neil]

Hello again:
I have been playing around again, and have revamped it. I have looked, played with and researched your naming suggestion, and have decided to stay with smaller field names. _ID will change to just ID but I can use PK but it feels strange to me.

Please look at the new jpgs' at https://flic.kr/p/KAXYFs and https://flic.kr/p/KVtLxn .

Over all this looks cleaner, but now 7 tables are even more similar (Lineage, Family, Formn, Branch, Service, Force, Govt). Could (should) these combined into one deep table call Aspect with 1 addition field (or Key) defining which Aspect it is? Could this or the 7 individual tables use a composted key base on the three FKs’? I don’t see any real reason, and 4 bytes per record is marginal in savings. What do you think?
Still wonder if I have done right in burying the date in the Change record and fear it might hard to fish out (speaking like Excel and not an Access person). Your thoughts?

The three small tables are future stuff that I need to work in, about haven’t study them to see how they should link in.

Again Thanks for Looking
All Comments welcomed
Neil