Results 1 to 11 of 11
  1. #1
    mainerain is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Location
    Maine, USA
    Posts
    146

    Relational Design

    I can't seem to master designing relations between these tables. Perhaps this isn't enough info but it would get very lengthy to explain all variables involved. Any suggestions would be appreciated.



    A StorageLoc can have MANY devices
    A Device can have MANY Events
    A Device has only ONE DeviceType
    A Device has only ONE DeviceDetail

    A MillNum can have MANY Loops
    A Loop can have MANY Events
    A Loop has only ONE LoopType
    A Loop has only ONE LoopDetail

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Very clearly explained. Thanks!

    The one to many joins MUST be in separate tables. Create relationships and apply referential integrity.
    The one to one joins could be in the same table but you may prefer to use separate tables. The relationships comment applies here as well if you use separate tables
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Are StorageLoc's related in any way to MillNum's ?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  4. #4
    mainerain is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Location
    Maine, USA
    Posts
    146
    Quote Originally Posted by mike60smart View Post
    Hi

    Are StorageLoc's related in any way to MillNum's ?
    not really. StorageLoc for spare devices
    MillNum for Loops

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Can you explain your business process which includes the use of Storage Locations and Loops ?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Are Device Events different than Loop Events?

    As Mike asked, can you give us a sample description of "a day at the office" that shows what and how these "things" that you know so well (and we don't) relate to each other in business setting?

  7. #7
    mainerain is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Location
    Maine, USA
    Posts
    146
    Oh boy. I can try.

    A StorageLoc can have MANY devices
    A Device can have MANY Events
    A Device has only ONE DeviceType
    A Device has only ONE DeviceDetail

    A MillNum can have MANY Loops
    A Loop can have MANY Events
    A Loop has only ONE LoopType
    A Loop has only ONE LoopDetail


    There are many different kinds of Devices. Each type of Device has it's own set of parameters that it is capable of. The parameters can vary greatly depending on the Type of Device. These Devices can either be kept in StorageLoc for spares or be inserted in Loops (Positions) for a specific application.

    There are many types of Loops which have there own specific parameters. Loop parameters can vary greatly depending on type of Loop. Only one Device can be used in a Loop at any one time.

    Events often contain the Loop and Device numbers, or other info not related to either. That's not the difficult part for me. Dealing with Devices and Loops as they relate to each other is.

    I could potentially have a table for all Devices with all the possible parameters but this would contain many empty fields. The same is true for Loops. If I make all possible fields for Loops there would be many empty fields.

    I thought about having an intermediate table (tblLoopTypes) that would link tblLoops to tblLoopDetails but I'm not sure if it would work or how to do it. The same possibly for Devices.
    Also how would I actually replace a Device for a Loop from a form if there is a "one to one" relationship (if that's what it is) where one specific loop has one specific device.

    Practical application:
    If a specific Device fails in a specific Loop I would replace it with a spare from StorageLoc. I would record the Event with the date, the Loop, the Device SN, notes, etc.

    I get frustrated because, far as I can tell, this doesn't fit the typical model of video's I have watched on Relationships. I feel if I could get this figured out then things would start to really click for me.
    There are so many variables i.e. between devices and there specific parameters and Loops and their specific parameters. I find it hard not to have "dead space" in tables.

    I am very thankful for your time and energy for trying to assist me. Let me know if you need further clarification.

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi
    If you can upload a zipped copy of your current database then we can take a look for you.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  9. #9
    mainerain is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Location
    Maine, USA
    Posts
    146

    narrowed the scope

    I'm not allowed to share file.

    I think I can boil it down to this:
    ( tblDetailA, tblDetailb, tblDetailC etc. ) ; tblLoopType ; tblLoop

    tblLoop contains basic common data
    tblLoopType determines which tblDetail? to connect to
    tblDetail? contains expanded data for tblLoop

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Sorry if you can't give us the actual names of the required tables then we cannot really help.

    tblDetailA, tblDetailb, tblDetailC etc. does not help at all.

    Can you post a screenshot of your relationship diagram?


    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  11. #11
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by mainerain View Post
    Oh boy. I can try.
    Looks like you describe an object model instead of a relational database.

    Maybe you have to create an hierarchical scheme, with tables of abstract objects on the top (devices/loops) and step-down with more specific objects that inherits the basic parameters from the "parent" objects (tables), and so on. Each object of a level, has its own properties and inherit the properties from the objects of the previous level in the hierarchical tree.

    For example, a motor is an electrical device. An oven also is an electrical device. Both could have the same basic electrical characteristics (Voltage, Current, Phases, Ac/Dc etc) but motor has RPM and more other characteristics that an oven doesn't. So, in this simplistic example, the table tblMotors and the table tblOvens could inherit the common basic characteristics by having the same FK that points to the tblDevices PK.

    I don't know if I was clear enough.

    Suggest you to make a research about "inheritance database scheme" to get more info.

    Good luck with your project,
    John

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

Similar Threads

  1. New Relational Database design
    By JonathanT in forum Database Design
    Replies: 27
    Last Post: 02-05-2020, 07:41 AM
  2. Relational problem
    By hakha4 in forum Access
    Replies: 3
    Last Post: 01-28-2018, 02:21 PM
  3. Relational help
    By anastam in forum Access
    Replies: 2
    Last Post: 03-07-2016, 03:00 PM
  4. Need to hire out relational database design
    By janakybrent in forum Access
    Replies: 5
    Last Post: 02-19-2016, 05:11 AM
  5. Relational Database Design Questions
    By mribnik in forum Database Design
    Replies: 40
    Last Post: 08-09-2011, 02:57 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