Many to Many - a separate table is a relationship?

[kkolodziej]

Hi!
Quick question about "many to many". Please help, because it overheats my brain 🔥

If I have a table with clients, a table with properties and a table with transactions, how many tables should I finally create? Should I keep the relationship between them in a separate table?

Assuming that:
- 1 client can own several properties (as owner)
- 1 property can have several clients (several owners)
- 1 transaction can have several customers buying (e.g. 2 customers buying)
- 1 transaction can have several selling customers (e.g. 2 owners are selling)
- 1 client can be seller in one transaction and buyer in the other one

I want to create one table with customers, without dividing them into sellers/buyers, but assigning them to several roles, e.g. in the view of a specific property, I will see a list of owners and a list of transactions related to this property.
In the transaction view, I will see the specific property, buyers and sellers.
How to do it? 🙂

[dbaum]

---

@kkolodziej wrote:

Should I keep the relationship between them in a separate table?

---

It sounds like your Transactions table serves this purpose. If each transaction row includes a Sellers column and a Buyers that each reference the Clients table, then the most recent transaction for a property represents the property's current owners. As helpful, you could use techniques like creating a Transactions slice comprising only the most recent transaction for each property or adding a column to the Properties table listing a property's current owners.

In that approach, the Transactions Sellers and Buyers columns would need to be EnumList type to comprise multiple values. Alternatively, you could use a perhaps more complex but more robust data structure: Make each of those a calculated column that uses REFROWS to list the pertinent rows from an additional child table where each row comprises a Transaction ID, a Client ID, and a Transaction Role (i.e., Seller or Buyer).

[kkolodziej]

Thank you for your answer!
I will choose the alternative option you provided. It will allow me to create a property and connect sellers to it before the transaction or in the absence of the transaction. The employee will see the owners of individual properties.

For sure:
- I create a table Customers with all customers
- I create the Real Estate table
- I create a "many to many" table where I save the property ID and the customer ID with the role of seller, if there are several sellers, then just a few rows.
- I create the Transactions table
- I create a "many to many" table where I save the property ID and customer ID with the buyer role, if there are more buyers, then there are several rows.

I think right?