I would like to find out what table relation would be idea for this case a Customer to Customer business transaction where one client provides a service to another for a charge. So this is purely a service C2C and no physical products involved. Examples of services include Math tutor lessons, yoga lessons, plumbing, piano lessons, karate lessons, art lessons, housekeeping etc.

Assumptions: 1. A transaction by a buying customer can contain multiple service (job) from the same seller customer. 2. A single (same) transaction from a buyer customer cannot contain multiple services (jobs) from multiple seller Customers.

What would be the idea data model for this case? What tables would I need, and what relationships between the tables would satisfy the stated case and assumptions?

Buyer 1->> transaction 1->> transaction_detail <<-1 jobs <<-1 Seller

I tried the above relationship with 1->> = one-to-many, making table transaction_detail a many-to-many table between the transaction table and job table. But this design allows for a transaction to contain multiple items from different sellers as shown below:

| Buyer_First | Buyer_Last | job_id | trans_id | JOB           | Seller_First | Seller_Last |
| Peter       | griffin    |     23 |      14 | English Tutor | Wei          | Tei         |
| Peter       | griffin    |     32 |      14 | Chinese tutor | Ping         | Wang        |

We can clearly see that transaction 14 contains two jobs from two different sellers, which is against our assumptions set above.

Any ideas?

  • But this design allows for a transaction to contain multiple items from different sellers as shown below It is 2 separate transactions. And each of them is to have unique trans_id. – Akina Oct 23 '18 at 9:07
  • It is the same transaction, 14, containing two jobs. trans_id and job_id are foreign key fields in the transaction_detail table. What exactly are you saying? – The Georgia Oct 23 '18 at 9:32
  • It is the same transaction, 14, containing two jobs They are dependent? for example, ejecting one of them automatically ejects another one? If so you're right. – Akina Oct 23 '18 at 9:38
  • job_id 23 and 32 belong to different Sellers, which means they should be in the same transaction/order/deal. – The Georgia Oct 23 '18 at 9:51
  • Again... some buyer want to buy 2 services of two different sellers. You start the transaction. You try to store first job... and it fails (for example, not enough cash - the buyer is blind). Will you try to store second job or not (buyer's cash is enough for this job)? – Akina Oct 23 '18 at 9:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.