Join Entity Attributes Solving Many to Many
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!
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.
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.
What we are solving is:
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.
|
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.
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.
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.
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.
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.
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.