Results 1 to 12 of 12
  1. #1
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147

    How far to Normalize

    Hi Guys
    Playing with my data (currently British WW 1 info) for import, and I have noticed that my tplTitles may not be setup optimal.
    Currently the table is:


    plTitles:=> TitleID Formal Full Short Abbv

    The unit which follows:
    1) had 6 versions during the war and this is the end war one (1918 Nov 11)
    2) it's 1 out of 78 divisions plus others units
    3) Each line is a (sub)Unit with 9+ tables referencing it.
    4) Every abbv. could (should?) be a Title record
    5) the number of repeated parts and abbv.

    So the question is Do I breakup Titles? and if so How?

    51st (Highland) Division
    X.51 T.M. Bty., R.F.A.
    Y.51 T.M. Bty., R.F.A.
    400th Eng Fld Coy., R.E.
    401st Eng Fld Coy., R.E.
    404th Eng Fld Coy., R.E.
    8th (Pioneer) Bn, R. Scots.
    255th (Highland) Bde., R.F.A.
    A Bty., R.F.A.
    B Bty., R.F.A.
    C Bty., R.F.A.
    D (H) Bty., R.F.A.
    256th (Highland) Bde., R.F.A.
    A Bty., R.F.A.
    B Bty., R.F.A.
    C Bty., R.F.A.
    D (H) Bty., R.F.A.
    152nd (1st Highland) Brigade
    152nd T.M. Bty.
    5th Bn, Sea. Highrs.
    6th Bn, Sea. Highrs.
    6/7th Bn, Gord. Highrs.
    153rd (2nd Highland) Brigade
    153rd T.M. Bty.
    6th Bn, R. Highrs.
    7th Bn, R. Highrs.
    6th Bn, A. & S. Highrs.
    154th (3rd Highland) Brigade
    154th T.M. Bty.
    4th Bn, Sea. Highrs.
    4th Bn, Gord. Highrs.
    7th Bn, A. & S. Highrs.

    Where some abbv. mean

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    "Normalize until hurts, denormalize until it works". Google it.

    Filed should be spelled Field.

    Exactly what are you trying to document with this db? The hierarchy of military organization?

    Not clear to me what the data elements are.

    Are there 5 fields as seems to be indicated by: TitleID Formal Full Short Abbv

    Yes, if your 'titles' are composed of components that have meaning implied by those element names, split up into separate fields.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Thanks June7
    Yes I have seen that quote before and believe in it, but I'm seeing repetitive parts but not clear fields therefore the question. If I subdivide Titles it feels like 4 (or 5) parts, with a bunch of nulls. Once broken, it maybe hard to write a scrip to join them altogether again. This al points (in my mind) to NO. The Black Watch in this period raised about 15 battalions so YES.

    Yes spelling is one reason for doing this DB. You are also correct about the hierarchy of military organization, but also Lineage, use of Resources, Organizational change.

    So four examples of the four fields in use would be:
    Formal Seaforth Highlanders (Ross-Shire Buffs, The Duke of Albany's) Princess Louise's Argyll and Sutherland Highlanders Black Watch (Royal Highlanders) 400th Field Engineer Company, Royal Engineers
    Full Seaforth Highlanders Argyll & Sutherland Highlanders Black Watch 400th Field Engineer Company, R.E.
    Short Sea. Highrs. A. & S. Highrs. R. Highrs. 400th Eng Fld Coy
    Abbv Sea H A&S H BW 400 Fld Coy

    Each column are some of the common ways of referring to the unit, depending on what is being presented.

    So the question is Do I breakup Titles? and if so How?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Okay, makes more sense now.

    You have 4 versions of a name for the same thing - aliases. Like my brother is named Robert but family call him Chip and his coworkers call him Bob and his son calls him Dad.

    If there will never be more than 4, then a field for each alias is probably manageable, although not strictly normalized.

    If aliases will have their own attributes that need to be documented (such as dates of use), need related tables.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Good, and yes you are on target. I had to draw the line somewhere and have chosen four. Started with two so long ago.

    Joining tblUnits and tblTitles is TblUnitTitles which contains a FrDate. Saying that I think I may of missed your point. Hmmm. Are you saying that Titles should be 3 fields (ID, String, Type) and new tables putting them together?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Wish I hadn't thrown in that last comment. Probably just disregard. But what I was indicating was possible need for a tblAliases that links to tblTitles. This would be if you want to allow any number of aliases and/or aliases have their own attributes.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Okay need to figure that table out. Will that solve my original question? If so thanks but Thanks anyway.

    I can see how the table will help data entry.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I think we decided you would not 'break up' titles.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    This is not about this post, but I have seen a lot of your postings in several subforums now, and I think here is something you maybe can use.

    The 1st posting from me here (after more than 10 years pause (I was active when there were forums which were accessed using Outlook Express) was
    https://www.accessforums.net/showthread.php?t=67768. I didn't get any answer and did find a solution myself instead, but somehow I remained in this forum.

    The SQL Server query in this post creates a structure description (a field where position of every component of product is described with structured string). As source is used a table, where for every part is defined a parent part (the part belongs to parent part). The structured string is composed of 2-character substrings, every substring defining a position of part in structure of parent part at according structure level. I.e. prod_pos_nr "00" describes the product itself, "01" is 1st sub_part (component) of product, "02" is 2nd sub_part of product, "0101" is first sub-part of sub-part described with prod_pos_nr = "01", etc. At first I used numbers for compose those 2-character substrings (what limited the number of possible sub-parts of any part with 99), but when in some products this number exceeded 140, I started to use 32-bit numbers composed of numbers and letters instead, increasing the max number of sub-parts to 1023 ("00" is not used for sub-parts).

    I think you can use this method to structure your units. Of course it will be more complicated, as you need the structure of your units at different time moments, not for current moment only, as in my products DB. Probably you need a query returning all units (from army to personell) with their parent units at wanted time moment, and a dummy unit structure table, which you clear at start, and into which you calculate the structure of wanted head unit at wanted time moment.

    Access doesn't support recursive queries, so when you want to take this path, you have to port the back-end of your DB to SQL Server, or you have to use VBA in Access to emulate recursive query. And calculating such structure is not fast - the SQL procedure calculating the structure for about 1000 products in my DB (the query is called for every product from cycle) takes about hour (and SQL Server is faster than Access). At same time, when the structure is calculated, it opens a lot of possibilities to analyze your data.

  10. #10
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Okay. So you saying 1..400+ F.E. Coy RE and 1..15 BW Title records? Each 4x columns wide.

    My alternative is stil to take something like the 400th Field Engineer Company, Royal Engineers, and break into 4 parts
    400th
    Field Engineer
    Company
    Royal Engineers

    and worry about the punctuation and pluralization later. UnitSize, UnitBranch and UnitParent of the last three should already be in TItles. These may not be useable relationships for TItles (order and twisted meanings), but the records will be useable.

    I am hearing you saying the many records (Frist) way the better way and will work with that idea.
    Thanks for the talk and the Alias table idea

  11. #11
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Sorry I didn’t see this early. Hmmm This makes my mind hurt, but I need to digest the idea before asking any questions.

  12. #12
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    The method used in your problem is a little fix at the top. I'm not good in SQL (yet), but I have done something similar in RDBMS and Cobol using Structures, Assemblies, Pieces and Parts with array multiplication. Yours goes a lot deeper and that is super cool. This is the method I was thinking when handling TOE's (Table of Organization and Equipment), but using it on Titles is an interesting thought. It could also handle the punctuation thingy.

    Access doesn't support recursive queries,
    This surprises me in that I thought it did (but not nicely). It doesn't like to draw them for sure. (Oh with VBA, no problem then )

    As a side note, I'm finding short threads get better responses then reusing or continuing an existing thread. It does make for a more fractured story and some repeated conversions, but each person brings a NEW thought or style to the problem. Even explaining it again, brings new thoughts to the problem. I have been thinking (okay, hacking) about this DB for a long while (guess 10+ years) off and on, and have tried (no code, just tables) many different setups. This time I'm just trying do it (1 point in time) and get off my Excel habit. (sounds like smoking ).

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 19
    Last Post: 11-06-2014, 07:31 PM
  2. I am so confused on how to normalize the data? please help???
    By coffayndtea in forum Database Design
    Replies: 2
    Last Post: 03-28-2012, 07:52 PM
  3. Normalize a flat database
    By worldwidewall in forum Access
    Replies: 5
    Last Post: 03-23-2012, 04:06 AM
  4. Using Query to De-normalize Data!
    By DNRTech in forum Queries
    Replies: 8
    Last Post: 03-13-2012, 01:53 PM
  5. To normalize or not
    By blazerboy6 in forum Database Design
    Replies: 5
    Last Post: 08-10-2011, 02:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums