Database structure: Would this structure work with this m:m?

Here is my issue: (Using MySQL)

I have 2 entities called 'shops' and 'clients'. I also have a M:M table between 'clients' and 'shops' called 'clients_shops' (CakePHP naming convention). The reason I am doing it this way is that this is a SaaS application where 'clients' may have many 'shops' and 'shops'开发者_StackOverflow社区 will definitely have many 'clients'.

However, I don't want to give a shop the ability to UPDATE/DELETE a 'client' record since what really needs to happen is that the 'shop' will EDIT/DELETE that 'client' from their own records, rather than from a master 'clients' table which is managed by the 'clients'.

Anyway, using this structure a 'shop' can run a query on the 'clients_shops' table to get a list of their clients and a 'client' can run a query a get a list of their 'shops'. Good so far...

So far, the database looks like this:

table.clients
client_id (PK, AI, NN)

table.shops  
shop_id (PK, AI, NN)

table.clients_shops  
clients_shops_id (PK,AI,NN)  
client_id (FK)  
shop_id (FK)

The ORM looks like this:

shops hasMany clients_shops  
clients hasMany clients_shops

So far so good (I think...) but here is my question. Let's say that there is a third table named 'trips'. The 'trips' table stores information on individual bookings whereby a 'client' will make reservations for a 'trip' that is provided by a 'shop'. This is where my brain is getting mushy. How should I set this relationship up?

Is it this way:

table.trips
trips_id (PK,AI,NN)
clients_shops_id (FK) [which would contain keys for both the shop and the client]

Or is there a better way to do this, like another table that uses clients.client_id AND clients_shops.clients_shops_id.

Thanks in advance to anyone that actually read this whole thing!

Unless it's required by your ORM, you don't need a surrogate foreign key for clients/shops and everything that refers to it.

Make a composite PRIMARY KEY instead and refer to it from elsewhere:

CREATE TABLE clients_shops
        (
        client_id INT NOT NULL,
        shop_id INT NOT NULL,
        PRIMARY KEY (client_id, shop_id)
        );

CREATE TABLE trips
        (
        trip_id INT NOT NULL PRIMARY KEY,
        client_id INT NOT NULL,
        shop_id INT NOT NULL,
        trip_data …,
        CONSTRAINT fk_trips_clients_shops
                FOREIGN KEY (client_id, shop_id)
                REFERENCES clients_shops
        );

This model assumes that you maintain clients/shops relationships separately from the clients' transactions and not let clients buy from the shops unless they are "related".

Probably you want the relationship to appear automatically whenever a trip is ordered by a client from a shop. In this case, you only need the second table, and the first table is a mere

SELECT  DISTINCT client_id, shop_id
FROM    trips

Here is the Logical Diagram to handle what you are looking for. Depending on your requirements you can change the non-identying relationships (Client::Trip & Shop::Trip) to identifying relationships. If you do though I would limit it to only changing the Shop::Trip to identifying though. Also make changes to the Cardinality as you see fit.

I would probably make the trips table like this:

table.trips
trip_id (PK)
shop_id (FK to shops)
client_id (FK to clients)
other_trip_column_etc

I would not reference the m-m table clients_shops from the trips table - just reference the shop and client tables with individual foreign keys.

The clients_shops table represents the current relationship between a client and a shop. The trip should not depend on these relationships, because they could potentially change in the future, and you probably wouldn't want the trip's data to change over time - it should be a transactional record that specifies exactly what shop, client, and trip was scheduled at that given time, regardless of the current relationship between that client and shop.