My database model has 2 particular tables inside it that need a many-to-many relationship. The tables are Systems and 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