7 Replies Latest reply on Apr 3, 2015 8:44 AM by philmodjunk

    Join Entity Attributes Solving Many to Many

    RockSure

      Title

      Join Entity Attributes Solving Many to Many

      Post

      When resolving a many to many relationship does the join table have to have any other attributes/fields other then the 2 foreign keys to work?

      If No would adding a 3rd field help it to be more effective?

      Additional thanks for all the other help on my other posts!

        • 1. Re: Join Entity Attributes Solving Many to Many
          philmodjunk

          The minimum requirements are that you have the two foreign keys. But additional fields in the join table are often useful. For one thing, a field in the join table records data to a specific pairing of records from the other two tables that simply could not be stored in either of the other two.

          Example:

          Invoices----<InvoiceData>-----Products

          Fields in InvoiceData would record the quantities and current unit prices both items you could not effectively store in a record from invoices nor from products.

          • 2. Re: Join Entity Attributes Solving Many to Many
            RockSure

            That is where I having trouble even getting the modeling clear in my mind

            Primary Tables
            USER
            SYSTEM
            LOCATION
            CODE

             

            First is the System and Location table a real entity or is it just a ValueList table for CODE
            where I have one USER to many CODEs and I really have a 2 primary table database with 2 ValueList tables.

            • 3. Re: Join Entity Attributes Solving Many to Many
              philmodjunk

              And how does system and location fit in to that?

              Isn't each specific to a given user's access to a particular system?

              If so, then you'd have: User----<Code>------System

              with one extra field in code to record the code that a given user uses to access that system.

              This does look familiar but I never assume info from previous threads--either I'll fail to remember correctly, or the design will have changed since the previous post.

              • 4. Re: Join Entity Attributes Solving Many to Many
                RockSure

                What we are solving is:

                       
                1. No unintentional duplicated access codes.
                       Some systems are limited to number of codes and therefore a user may have the same code as another use. An example may be a garage door wireless keypad opener. 1 main code and 1 temp code but you may have given both or either to 9 different people.
                2.      
                3. Systems or Categories for the lack of a better descriptor. Some what finite but would need to add another if we add something not yet seen. Like they may add biometric key entry systems.

                We have about 10 different systems. Example of what I call a system is Alarm, Gate Access, Cameras, Access Points, Key Keepers, Master Key System, Routers, Apps/Software, Audio Video, Keys.  

                       
                1. Locations are where the Systems points of access are . For gates they would be Front, Side or Maintenance. Access points could be Main Residence, Barn, Rec House, Guest House 1.
                        
                2.      
                3. Sub Locations are not always used as shown below.
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
                               

                USER

                               
                               

                SYSTEM

                               
                               

                LOCATIONS

                               
                               

                SUB_LOCATIONS

                               
                               

                SubSub

                               
                               

                Code

                               
                               

                 

                               
                               

                BILL

                               
                               

                GATE

                               
                               

                FRONT

                               
                               

                NONE

                               
                               

                 

                               
                               

                 

                               
                               

                 

                               
                               

                Bill

                               
                               

                Alarm

                               
                               

                Main Resident

                               
                               

                NONE

                               
                               

                 

                               
                               

                 

                               
                               

                 

                               
                               

                Bill

                               
                               

                Master Key

                               
                               

                Main Residence

                               
                               

                Garage Entrance

                               
                               

                 

                               
                               

                 

                               
                               

                 

                               
                               

                Bill

                               
                               

                Access Point

                               
                               

                Main Residence

                               
                               

                WiFi 1

                               
                               

                SSID Name

                               
                               

                 

                               
                               

                 

                               
                               

                Harry

                               
                               

                Access Point

                               
                               

                Maintenance Garage

                               
                               

                Office

                               
                               

                 

                               
                               

                 

                               
                               

                 

                               
                               

                Bill

                               
                               

                Audio Video

                               
                               

                Main Residence

                               
                               

                Family Room

                               
                               

                TV

                               
                               

                 

                               
                               

                 

                               

                 

                So with this info would I be on the right track as follows?

                So it seems to me that while System, Location, Sub and SubSub Locations are all attributes of Code and should reside each respectively in its own table but relate as a value list and not part of the actual structure per say like a user would relate to the code table. Like one user with many codes.  And while some of the actual values in codes in the codes field may get duplicated for convenience purpose they are not set-value fields.  

                Still old Access floating in my head so still very fuzzy. 

                 

                • 5. Re: Join Entity Attributes Solving Many to Many
                  philmodjunk

                  You seem to have difficult nut to crack as specified by these two statements:

                  No unintentional duplicated access codes.
                  Some systems are limited to number of codes and therefore a user may have the same code as another use.

                  What is not clear is if a user needs to be linked to 3 systems, do you give them the same code for all 3 in every case or do you (at least some of the time) specify different codes for different systems.

                  If it's the latter, then the codes table cannot serve as the join table between users and systems. And it looks like you need to set up a system that checks for duplicate values, but allows you to override that warning when you want this to be a case of an intentional duplication. I suspect a self join on the code field itself so that you can get a count of how many records with the same code exist will be the better option for doing that part of the process.

                   

                  • 6. Re: Join Entity Attributes Solving Many to Many
                    RockSure

                    Just talking through this is helping me quite a bit.

                    So a User will always have a code and a code will always have a system but a user will never have a system without a code.  The Code and System live together. I think this is telling me the System is a describer of the code and therefore a attribute of code.

                     

                    Since a code will always have a system and a System will always a Location therefore a User would also never have a Location without a code and System. I think this is telling me the Location is also further a describer of code and also a attribute of code.

                     

                    A Sub-Location and Sub-Sub Location may or may not exist and again is just a further describer of code and also being an attribute of code.

                     

                    Potentially the CODE value can be unique for each Code up to the limits of a system. An example of a system that is limited is a garage door keyless keypad entry which only allows 2 codes however you may have given that code to 10 users. An Elective example  scenario would be a user wanting the same codes for one system with mulit locations and or the same code for more than one system. An example of that is a user that’s the owner that has 4 Maintenance Barns with separate "ALARMS" (which is the system) as well as alarms at the guest house and main house. Even if  all those systems have the capacity to have different codes the user/owner would elect to have the same code across the board. Lets add 5 gates and if the system allows the same number of digits then potentially all his 5 gate codes could be the same as the 6 alarms.  

                    Now I currently have a field in the CODE table named Code set to Always Validate, Unique,   Allow Override and when I type in a duplicated pass code it pops up and asks me if I want to allow and override it. With this working would there be a reason to create a self join?

                     

                    I would like to run the following reports.

                           
                    1.  Show all pass codes ever issued to a single user.
                    2.      
                    3. Show all users and locations based on a single pass code. If a code was compromised it would show me all the systems and locations and users that it would effect and have to get change.

                     

                    Based on this would you say that I have a One to Many Table that does not need a join table

                    USER---->CODE  ?

                    And the rest of my tables are in fact Value-List tables?

                    And that I can run the reports I listed?
                    Is the structure and design of the database sound?

                    Is my thinking correct?

                     

                    Thanks again for the help I do think I am learning quite a bit and fast and I truly appreciate your  patients reading my long post. 

                    • 7. Re: Join Entity Attributes Solving Many to Many
                      philmodjunk

                      I would see location as an attribute of System and Code an attribute of both User and System as a given user will have only one code for a particular system. That's why I see using a table of codes as a join table between User and System as an option.

                      The possibility that you might grant the same code to two users of a given system raises the possibility that you need a "star join" where a record in the join table links to user, system and code. Or it may just be that you can keep the code as the join table, but use interface design and a self join to keep from issuing an unintentional duplicate code.