My database model has 2 particular tables inside it that need a many-to-many relationship. The tables are
Users. One user could be a member in excess of one system and something system has several user inside it.
system_id may be the PK within the system table and
user_id may be the PK within the user table. I've them related to
system_id like a FK within the user table. I can not appear to determine a table among the 2. I'd the idea of developing a
system_user table and taking advantage of both foreign secrets inside but cant observe how that will work.
You first got it right. Make use of a mapping table for a lot of to a lot of associations. You won't need any foreign key posts inside your Systems or Customers tables. Your mapping table may have something similar to a MappingID PK, SystemID, and UserID. You might make a whole row the PK, nevertheless its unnecessary (should you did not desire to use the MappingID). Then, simply place pairs into this table that connect a method having a User. You are able to choose by SystemID or UserID to obtain all of the associations a particular entity has. You may even include any metadata the mapping may have, such as the date the association was established.
You are headed within the right direction.
Produce a table "system_user":
CREATE TABLE system_user (system_id INT NOT NULL, user_id INT NOT NULL);
This isn't correctly an entity inside a strict relational modelling sense, therefore it does not need a primary key. However, you can include one in case your situations want it.
To obtain all the customers inside a system you'd query like so:
SELECT u.* FROM user u, system_user su WHERE su.system_id = ?
To obtain all the systems a person can achieve, you'd query like so:
SELECT s.* FROM system s, system_user su WHERE u.user_id = ?
Hope this can help!
System System_User User ------ ----------- ---- *system_id ---> *system_id data *user_id <--- *user_id data * denotes the main key posts ---> denotes a PK/FK relationship
You are on course. Your brand-new system_user table would at least have 3 posts:
- systemuser_id - primary key
- user_id - foreign answer to user table
- system_id - foreign answer to system table