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

    Creating a second Path to another Table

    Hello all, I have attached a ERD sketch of the problem.
    Click image for larger version. 

Name:	Eqpt Question 190121.png 
Views:	20 
Size:	30.6 KB 
ID:	37054

    EqptFK in E_tblPers creates a 2nd relational path between E_tblPers and E_tblEqpts. In studying the data I find 99.9% of E_tblPers records have 1 piece of eqpt, and less than 10% have more than 1 item. Most items are pooled within the E_tblElmts (not shown) though E_tblEimtEqpts.

    Looking at this problem I see three possible design solutions:
    a) Use EqptFK and create the relationship between E_tblPers and E_tblEqpts. (save space in E_tblPerEqpt as 80%+ records of E_tblPers are not entered)

    b) Drop EqptFK saving the confusion of two paths to E_tblEqpts. (this is 2019, and disk space is no problem)

    c) Something better out of left field.



    THe questions is which one is the "Proper" way. I know a this is one of thoses questions that can become personal choice, but Experience trumps Knowledge.

    Thanks for looking

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I always use 1 to Many tables;
    1 person has many phones,
    1 person has many payments, etc
    (tPers to tEqpts)

    tho I'm not sure what E_tblElmtEqpts does. It seems Qty could just be part of E_tblEqpts and eliminate that extra table.

  3. #3
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Thanks.
    You do realise that I have it setup as 1 Epqt (type of desk) has many owners, and the other path is a Many to Many? Saying that I can understand the confusion.
    1 desk used by many people (Shared desk ??)
    vs
    1 desk type used by many people (Shown)
    The I left out and implied the word type. (sorry).

    After all that you are saying stick with the Many to Many and drop the single one to many? (i will assume yes)

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I have been looking at the relationships for a while.
    Click image for larger version. 

Name:	WesternNiel1.png 
Views:	11 
Size:	53.0 KB 
ID:	37086
    I don't understand why field "EqptID_FK" is in table "E_tblPers".
    I don't know the purpose of table "E_tblPers"; I am guessing it is a personnel table. (although I don't see fields for FName or LName)


    My question is: If table "E_tblPers"is about personnel, how is "EqptID_FK" an attribute of a person?



    If you removed the field "EqptID_FK" from table "E_tblPers", the relationships would be
    Click image for larger version. 

Name:	WesternNiel2.png 
Views:	12 
Size:	49.2 KB 
ID:	37087
    Better IMHO....



    My $0.02................

  5. #5
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Hi Steve, Thanks for looking. Table "E_tblPers" is about personnel, but more about position than a person, hence no FName LName fields. In a military organization everyone has a weapon, hence my thought about having "EqptID_FK", but some people get assigned some additional stuff (because of position/role).

    I have been thinking hard on this "shower" idea, and I'm leaning toward dropping the
    EqptID_FK in E_tblPers. It just isn't saving enough resources and creating a possible path of misuse / forgetfulness / confusion.

    I haven't really fleshed out E_tblEqpt yet but will keep in mind that Type and Data are reserved words. Thanks. I don't know if I will need ~18 different rifle marks with small differences or 1 universal one (I know it will be in the middle), but that can be filled in latter.

    Again thanks for Looking

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Western_Neil View Post
    Table "E_tblPers" is about personnel, but more about position than a person, hence no FName LName fields.
    OK, that clears that up.
    "EqptID_FK" is not an attribute of personnel, so my vote (if I had a vote ) would be to remove it. The junction table "E_tblPerEqpts" is the correct place for that field.
    Well, that is where I would put it.


    Good luck with your project....

  7. #7
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Thanks and voting is what I wanted.
    Thanks you and everyone helps

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

Similar Threads

  1. Replies: 2
    Last Post: 08-22-2018, 12:15 PM
  2. Universal path for exporting table
    By kspabo in forum Access
    Replies: 5
    Last Post: 08-06-2014, 05:03 AM
  3. Replies: 6
    Last Post: 05-27-2014, 05:41 AM
  4. Replies: 3
    Last Post: 11-24-2012, 08:56 AM
  5. Display Linked table Path
    By gg80 in forum Access
    Replies: 3
    Last Post: 10-14-2011, 10:02 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