Results 1 to 9 of 9

There is probably a good RDB way of doing this, point to 2 different tables from same column...

  1. #1
    oransen is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2019
    Posts
    4

    There is probably a good RDB way of doing this, point to 2 different tables from same column...

    I have a table with 2 key columns. The 2 key columns are a lookup into a 3rd, the 3rd column is the "answer".



    This is a mechanical factory type application.

    The first two columns are SAP codes (8 character codes) for physical objects. The 3rd column is the time required to fix the two physical objects together.

    For example here are two rows from the table

    Thing1 Thing2 Time to glue together
    TUBE5678 TUBE3241 33
    VALV5678 TUBE3311 102
    VALV5678 VALV3311 102


    Now my problem is that some 8 character SAP codes correspond to tubes of varying diameters, thicknesses and materials and they exist in the Tubes table.

    Other SAP codes are distinct objects (valves etc), in an Objects table.

    I.e. the first two columns are foreign keys into other tables.

    It is mechanically possible to fix:
    1. a tube to a tube
    2. an object to an object
    3. a tube to an object.


    Before the introduction of VALVes it was easy, each 8 character code in the first two columns corresponded to a single row in the Tubes table.

    But with the introduction of VALVes I'm not sure how to keep the RDB database design "clean", because the 8 character code could be in the Tubes table or in the Valves table.

    I'm no DB expert, so thanks in advance for any help or pointers


    Owen
    Last edited by oransen; 05-27-2019 at 06:43 AM.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,327
    Owen,
    Welcome to the forum.

    Please step back and tell readers about your business in simple, English --avoid jargon. Remember we don't know you, your business, your environment and have no idea what you are working with nor how you have approached something.
    Your SAP is confusing-- for example SAP is a company;SAP deals with software,....I don't think that's what you are referencing.
    Objects is quite generic, but has significance in the database area(Object-oriented)--again I don't think that is what you are referencing.
    Pretend you are in a line at McDonalds, someone asks what do you do?, what are you working on at the moment? and, ignoring the cheekiness of this person, what would you say?
    Remember, this person
    -doesn't know you,
    -has never heard of SAP
    -has never been involved with database
    -doesn't know your business
    -has no idea of your environment.

    Real simple terms until readers understand the context of your post.

    Or post a copy of your database along with a clear description of an issue(s) so we can experience that issue(s).
    Last edited by orange; 05-27-2019 at 06:37 AM. Reason: additional info

  3. #3
    oransen is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2019
    Posts
    4
    Thanks for the tips, I've edited my original post to make it cleare, I hope!

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,327
    Sorry, it has clarified a little for me.

    Seems you assemble/glue 2 items and have identified the time to do so.

    How about trying to give this a shot?

    Pretend you are in a line at McDonalds, someone asks what do you do?, what are you working on at the moment? and, ignoring the cheekiness of this person, what would you say?
    Remember, this person
    -doesn't know you,
    -has never heard of SAP -(what does SAP mean to you?)
    -has never been involved with database
    -doesn't know your business
    -has no idea of your environment.
    Last edited by orange; 05-27-2019 at 07:06 AM. Reason: revised

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,349
    Are valves in Objects or Valves table?

    Are fields different in the tables? Could there be one table?
    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.

  6. #6
    oransen is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2019
    Posts
    4
    Quote Originally Posted by June7 View Post
    Are valves in Objects or Valves table?

    Are fields different in the tables? Could there be one table?

    I've found an answer, I need to use super-types and sub-types, as shown in this example:


    Click image for larger version. 

Name:	Sub-Types-Super-Types.gif 
Views:	16 
Size:	32.3 KB 
ID:	38512

    "Employees" covers both "AdministrativePersonel" and "AcademicPersonel". In my case I'd have "Materials" as the super type, and two sub-types "Tubes" and "Valves".

    The keys in all three tables will be 8 digit material codes.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,327
    Owen,

    Can you provide the link to the thread where you found this attachment?
    I'm interested in sub/super types (generalization/specialization). You often see the theoretical info, but there are few physical database examples. I'd like to see your database when you get it designed with sub/super.

    Here are some links that may help with the sub/super project.
    https://stackoverflow.com/questions/...970646#4970646
    https://www.javaguicodexample.com/er...delnotes1.html
    http://access.mvps.org/access/tables/tbl0013.htm
    Last edited by orange; 05-28-2019 at 12:00 PM.

  8. #8
    oransen is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2019
    Posts
    4
    Quote Originally Posted by orange View Post
    Owen,

    Can you provide the link to the thread where you found this attachment?
    Here you go:

    https://social.msdn.microsoft.com/Fo...orum=accessdev

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,327

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

Similar Threads

  1. Replies: 1
    Last Post: 12-03-2017, 05:13 PM
  2. Replies: 3
    Last Post: 08-08-2013, 01:43 PM
  3. Replies: 6
    Last Post: 01-13-2012, 09:17 AM
  4. Replies: 86
    Last Post: 10-07-2011, 06:06 AM
  5. Good programming practice wrt lookup tables?
    By Buakaw in forum Programming
    Replies: 10
    Last Post: 03-19-2011, 10:33 AM

Tags for this Thread

Posting Permissions

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