Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    FALAGELOLOGIST is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    14

    Question Request For Help With Data Structure - Are Four-Way Composite/Junction Tables Feasible?

    Disclaimer: Relatively new to access

    I am creating a database with four tables, all of which have many-to-many relationships with each other.

    Table A is circuits
    Table B is modules
    Table C is encryption
    Table D is verification code

    Again, many to many relationships between all four tables. Each table has more fields than just "ID" and "Title" which is why a database is better than a spreadsheet, but for the sake of this example let's pretend they don't. Four fields with just ID and Title.

    Currently all the data is being stored in a spreadsheet with about 3,000 records, each with a different combination of a value from table A, B, C, D. The same A can live on different Ds and vice versa. The same Cs can live on different Bs and vice versa. Many-to-many all four ways.

    When I started I created six composite tables -

    (tblc = table composite)
    tblC_A_B
    tblC_A_C
    tblC_A_D


    tblC_B_C
    tblC_B_D
    tblC_C_D

    But this just seems so excessive and likely a mess when it comes to data entry and management.

    In my head it makes sense to just have one composite table with four fields - ID_A, ID_B, ID_C, ID_D and then link the primary keys of each table to the corresponding ID field. But when trying to research composite tables, I haven't been able to find anything about a composite table used for more than linking two tables. Is doing so bad practice?

    My question is, what is the best way to link the data? One composite table? What effect does this have on using a form to modify values? Any advice is HIGHLY appreciated. I am at a loss on how to build this thing.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tell us about the business in simple English - no jargon. You identified 4 tables. What is the business entity associated with each of these proposed tables? What exactly links one of these proposed tables to another -- in plain English?

    Relatively new to access
    Do you have any experience with database?

  3. #3
    FALAGELOLOGIST is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    14
    Click image for larger version. 

Name:	DBStructure.png 
Views:	34 
Size:	22.6 KB 
ID:	29565Or perhaps this is closer to what I am looking for?

    I am helping a client track various elements of a security network for a sales company.

    There are physical circuits out there. Each circuit has modules on it. Those modules need codes to work. They can't work unless each code has the same encryption as the module does.

    Circuits hold multiple modules. But two identical modules can live on different circuits. Codes living on one identical module will be different than the codes living on the other identical module if those modules are on different systems. Two or more identical module can also have different encryptions and they will only work if the codes on those modules have the same encryption as well.

    I have made several Access databases, but I am very grassroots and have never handled more than one many to many relationships before. Typically data structure is the part I find easiest when creating. For this new one, it is tough! Please let me know if I can be more specific.

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    But two identical modules can live on different circuits.
    I think that is the bit that is confusing you. The fact that two modules are identical is actually irrelevant here - they are still separate units. From your description, I get these "rules" right away:

    - One circuit contains one or more modules
    - A module can be on only one circuit (this one is critical - do I have it right?)

    Your description are a bit vague, so some of these may not be right:

    A module has only one code (which might be a "set" of codes?)
    A module has only one encryption
    A Code (or "set" of codes) has only one encryption
    One encryption can apply to many modules
    One encryption can apply to many codes (or "sets" of codes)

    There may be others that I have missed - but so far I don't see ANY many-to-many relationships, but that is from your description, not from the diagram. The diagram DOES show a many-to-many, but I'm not sure it's right.

    Can you clarify some of these?

  5. #5
    FALAGELOLOGIST is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    14
    Quote Originally Posted by John_G View Post
    I think that is the bit that is confusing you. The fact that two modules are identical is actually irrelevant here - they are still separate units. From your description, I get these "rules" right away:

    - One circuit contains one or more modules
    - A module can be on only one circuit (this one is critical - do I have it right?)

    Your description are a bit vague, so some of these may not be right:

    A module has only one code (which might be a "set" of codes?)
    A module has only one encryption
    A Code (or "set" of codes) has only one encryption
    One encryption can apply to many modules
    One encryption can apply to many codes (or "sets" of codes)

    There may be others that I have missed - but so far I don't see ANY many-to-many relationships, but that is from your description, not from the diagram. The diagram DOES show a many-to-many, but I'm not sure it's right.

    Can you clarify some of these?
    Circuits have many modules. The same module can live on many circuits. The module will have different code/encryption depending on what circuit it lives on.

    Example:
    Circuit "Batman" has modules "Lion", "Tiger", and "Bear"
    Circuit "Superman" has modules "Dog", "Cat", "Lion" and "Bear"
    The encryption/codes on "Lion" and "Bear" are different for the one living on "Superman" than they are for the one living on "batman"


    A module has many codes on it.
    A module has only one encryption, but module XYZ on Circuit B will have a different encryption than module XYZ on Circuit C. Same codes for the set of codes living on the module.
    A group of codes that live on one module will all have the same encryption, but one of the codes from that group can live on another circuit's module with a different encryption
    One encryption can apply to many modules, but the same module can have a different encryption if a copy of the module lives on a different circuit

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    For clarity, using your samples, can you provide mock-up codes and encryptions to complete/extend your description?

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Table A is circuits
    Table B is modules
    Table C is encryption
    Table D is verification code

    So, what I see so far might be something like this:


    1. Each Circuit (A) can have one more modules (B) (I'll call them table AB)
    2. Each circuit-module AB has one encryption C1
    3. Each circuit-module AB has one or more codes (D) (I'll call that table ABD)
    4. Each circuit-module-code (ABD) has an encryption C2, where C1 = C2.


    Table A (Circuit)

    ID_A (PK)
    ... Other fields describing the circuit

    Table B (Module)

    ID_B (PK)
    ... other fields describing each module type (but not encryption)

    Table AB (Circuit Module)

    ID_AB (PK)
    ID_A (FK to circuit)
    ID_B (FK to module description)
    C1 (FK to encryption)
    ... other fields describing this module occurance

    Table ABD (Circuit Module Code)

    ID_ABD (PK)
    ID_AB (FK to Circuit-module)
    ID_D (FK to Codes)
    C2 (FK to encryption) (C2 = C1)

    Table C (Encryption)

    ID_C (PK)
    ... other fields describing the encryption

    Table D1 (Codes)

    ID_D1 (PK)
    ... other fields describing this code

    Table D2 (code encryption)

    ID_D2 (PK)
    ID_D1 (FK to D1, code description)
    ID_C (FK to Encryption - indicates the encryption used in this module)


    That, I think should almost be it. Notice I have used no many-to-many relationships. There is no many-to-many relationship between codes and encryption, because any one occurance (or use) of a code can have only one encryption. Different occurances of the same code can have different encryptions as you said, and that is covered in table D2. All the FK links to code or encryption act as lookups for detailed information.

    That's my take on what you have; I'm sure there are others. Let us know how it goes.

  8. #8
    FALAGELOLOGIST is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    14
    Quote Originally Posted by John_G View Post
    Table A is circuits
    Table B is modules
    Table C is encryption
    Table D is verification code

    So, what I see so far might be something like this:


    1. Each Circuit (A) can have one more modules (B) (I'll call them table AB)
    2. Each circuit-module AB has one encryption C1
    3. Each circuit-module AB has one or more codes (D) (I'll call that table ABD)
    4. Each circuit-module-code (ABD) has an encryption C2, where C1 = C2.


    Table A (Circuit)

    ID_A (PK)
    ... Other fields describing the circuit

    Table B (Module)

    ID_B (PK)
    ... other fields describing each module type (but not encryption)

    Table AB (Circuit Module)

    ID_AB (PK)
    ID_A (FK to circuit)
    ID_B (FK to module description)
    C1 (FK to encryption)
    ... other fields describing this module occurance

    Table ABD (Circuit Module Code)

    ID_ABD (PK)
    ID_AB (FK to Circuit-module)
    ID_D (FK to Codes)
    C2 (FK to encryption) (C2 = C1)

    Table C (Encryption)

    ID_C (PK)
    ... other fields describing the encryption

    Table D1 (Codes)

    ID_D1 (PK)
    ... other fields describing this code

    Table D2 (code encryption)

    ID_D2 (PK)
    ID_D1 (FK to D1, code description)
    ID_C (FK to Encryption - indicates the encryption used in this module)


    That, I think should almost be it. Notice I have used no many-to-many relationships. There is no many-to-many relationship between codes and encryption, because any one occurance (or use) of a code can have only one encryption. Different occurances of the same code can have different encryptions as you said, and that is covered in table D2. All the FK links to code or encryption act as lookups for detailed information.

    That's my take on what you have; I'm sure there are others. Let us know how it goes.
    I did my best attempt but I think I still did so incorrectly. Does this look close?

    Click image for larger version. 

Name:	DBStructure_2.png 
Views:	24 
Size:	40.7 KB 
ID:	29597

    I will upload the database I am working with as well with mock data. I included my four-way junction/composite table which I have been working with. I tried to create that using the structure you provided but I am seemingly unable to. I think we we might be going wrong is "2. Each circuit-module AB has one encryption C1" The same circuit/module can have more than one encryption on it. You really can't infer anything unless you have all four pieces of data. I will upload a half-assed remake of the structure provided to me by the client, but they really don't know much about databases and I feel like it has actually been hurting me more than helping me.Click image for larger version. 

Name:	Client_Provided_Structure.png 
Views:	24 
Size:	13.5 KB 
ID:	29598

    Here is a link to my sample database - https://www.dropbox.com/s/denbpw4gcb...elp.accdb?dl=0

    I want to recreate "TblC_All" but using the correct table structure for what I am trying to do. All help is GREATLY appreciated.

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I downloaded your database. Do you have a definition/description for Code and Encryption from the users? If so, please post it.
    It may help you and readers if you could tell us what you have learned regarding the tables and relationships in business terms.

    It might also help us to focus by limiting the number of items under review until we get some to work as the user/client expects.
    I don't think 30 circuits and 400+ modules are needed to do some analysis.
    In your model above in post #9, I don't think the link between module and code is correct (but more guess/reasoning than fact).
    It seems a module gets encrypted by means of a Code.
    Can you restate this
    The encryption/codes on "Lion" and "Bear" are different for the one living on "Superman" than they are for the one living on "batman"
    ? Perhaps give us a mock up of the "codes" so we can test the evolving model.

    It would be interesting to see what description is considered appropriate/relevant for each of the junction tables.

    Good luck --interesting project.

    Here are some definitions (Google)

    Database encryption can generally be defined as a process that uses an algorithm to transform data stored in a database into "cipher text" that is incomprehensible without first being decrypted.

    The translation of data into a secret code. Encryption is the most effective way to achieve data security. To read an encrypted file, you must have access to a secret key or password that enables you to decrypt it.

  10. #10
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Back in post #3 (referring to codes) you wrote: They can't work unless each code has the same encryption as the module does.

    But in the post above you have: The same circuit/module can have more than one encryption on it.

    Those two seem to be contradictory, and lead to the question "When a code is on a module, which encryption does it need?"
    If a code is on a module that can have multiple encryptions, it has to match one of them, but which one? Does it matter?

    I have downloaded the database too. I don't think you need the junction table between codes and encryptions (tbl_DC_Code_Encryption). The data in those (I think) just describes the each code, and describes each encryption. The required data is in tbl_ABD_CircuitModuleCodes, which indicates for each module, the codes it needs and the encryption it must have.

    But except for that it's looking good (but see the question above re: multiple encryptions on one module).

    Now one thing - PLEASE get rid of the lookup fields on your tables and replace them with regular one-to-many FK fields to match your diagram. Lookup fields can lead to all kinds of difficulties, not the least of which is that they are next to impossible to use in queries.

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Totally agree with John_G re: getting rid of Lookup fields in tables.

    Just as you have described
    Circuit "Batman" has modules "Lion", "Tiger", and "Bear"
    Circuit "Superman" has modules "Dog", "Cat", "Lion" and "Bear"


    Can you concoct/show the "encryption/algorithm" (or whatever the user/clients defines) and the code (for demo purposes)
    to extend
    The encryption/codes on "Lion" and "Bear" are different for the one living on "Superman" than they are for the one living on "batman"

    Here is a link to some codes and algorithms

  12. #12
    FALAGELOLOGIST is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    14
    First off thank you everyone for being so helpful, this is awesome. Secondly I apologize for the ambiguity but I can only give away so much information. The stakeholders in charge of the codes do not talk to the stakeholders in charge of the circuits. We support the overall effort but are only provided spreadsheets of the data (despite our best wishes better resources) all of which come in separate formats (acronyms spelled out first not spelled out, dashes instead of spaces). The master spreadsheet is a mess which is why the database is being built.

    Back in post #3 (referring to codes) you wrote: They can't work unless each code has the same encryption as the module does.
    But in the post above you have: The same circuit/module can have more than one encryption on it.

    Those two seem to be contradictory, and lead to the question "When a code is on a module, which encryption does it need?"
    If a code is on a module that can have multiple encryptions, it has to match one of them, but which one? Does it matter?
    I will see if I can answer that question when I have access to the data tomorrow. I think I may have gotten what I'm looking for below.

    Click image for larger version. 

Name:	DBStructure_3.png 
Views:	20 
Size:	23.2 KB 
ID:	29615

    I think this is what I want! If it works well then I greatly appreciate the help. I will report back with an update.

    And I planned to convert the lookup columns to integers. It's just easier to do that right now when copy/pasting from a spreadsheet and I am messing with the table structure too much to use queries.

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I came up with this, but really need to understand codes and encryption and more info on the business. The data can be mocked up(fudged) but we need to know what is used generally, how it is used in your business and what is expected - for testing.

    Click image for larger version. 

Name:	CircuitsModulesCodes.jpg 
Views:	20 
Size:	57.5 KB 
ID:	29616

  14. #14
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    "The stakeholders in charge of the codes do not talk to the stakeholders in charge of the circuits."
    Doesn't that sort of thing just drive you nuts?! And then they wonder why the database doesn't do what they expect it to. Sigh....

    The structure looks good. It will need a minor change if a module can have multiple encryptions on it. The requirement that codes and modules must have matching encryptions is more a data constraint than it is a relationship, so it could be enforced with just a little bit of VBA.

    You're getting there - good luck.

  15. #15
    FALAGELOLOGIST is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    14
    Quote Originally Posted by orange View Post
    I came up with this, but really need to understand codes and encryption and more info on the business. The data can be mocked up(fudged) but we need to know what is used generally, how it is used in your business and what is expected - for testing.

    Click image for larger version. 

Name:	CircuitsModulesCodes.jpg 
Views:	20 
Size:	57.5 KB 
ID:	29616
    Actually... I think this is it! Again I will report back. @John_G yes absolutely!! It truly amazes me sometimes. Appreciate the help all!!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Composite key relationship structure
    By rbennion in forum Database Design
    Replies: 6
    Last Post: 07-07-2017, 09:08 AM
  2. Replies: 3
    Last Post: 07-02-2015, 09:15 AM
  3. Data entry form with three junction tables?
    By justgeig in forum Forms
    Replies: 5
    Last Post: 08-13-2013, 04:23 PM
  4. Replies: 4
    Last Post: 03-12-2013, 01:59 PM
  5. Replies: 10
    Last Post: 07-12-2011, 11:09 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