2

I have a transaction table that have a structure like this:

| transaction_id | customer_id | amount | transaction_type_id | status |
transaction_id => is the id of the transaction
customer_id => is the customer id who did the transaction
amount => Total transaction amount
transaction_type_id => Type of transaction 
status => status of the transaction => pending, complete, canceled

I want to create a split bill feature for my program, the feature works like this:

  1. Customer input the total amount of the bill that wanted to be split.
  2. Customer add another customer (n customers) to split the bill, and specify the amount.
  3. Transaction will be made for all customers in split bill with status pending and amount specified.
  4. The split bill request will be received, by other customers to reject it or to accept it
  5. If one of the customer reject the split bill request, the transaction will be canceled, but if all the customer accept the request, the transaction status will be complete

so far I've came up to solve this solution, by creating the main split_bill table and split_bill_request like this:

split_bill
split_bill_id|transaction_ids | initiator | total_amount
split_bill_id => id of the split bill
transaction_ids => All the customer transaction id separated by comma
initiator => the customer_id who initiate split bill
total_amount => total of the bill

split_bill_request
split_bill_request_id | split_bill_id | customer_id | status
split_bill_request_id => id of split bill request
split_bill_id => id of the split bill
customer_id => id of the customer who receive split bill request from initiator
status => status of the request pending,accept,reject

I don't know if this is the right way to do it, can you suggest another approach, because I have a doubt with transaction_ids in split_bill table or the efficient way to do this, and because I don't have permission to modify the transaction table?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
blue
  • 1,695
  • 3
  • 10
  • 17
  • 2
    This question is quite subjective, as are all design related questions. Some food for thought on the transaction ids field: https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Shadow Dec 03 '18 at 06:33
  • They're only 'requests' because they're 'pending'. But in reality, you're not going to move them to some separate table, so I think it's unhelpful to store them as 'requests'. They're just 'splits', albeit unconfirmed ones. – Strawberry Dec 03 '18 at 06:55
  • Oh, and the 'transaction ids' idea is a terrible one! – Strawberry Dec 03 '18 at 07:05

0 Answers0