Join Entity Attributes Solving Many to Many

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.

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. 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.  

     
3. 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.
         
     
4. 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. 

 

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. 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.