0

Is there a way to make a column that can have multiple value of id from one table. I want to create a transaction table where user can buy multiple items in one transaction receipt. Iam using MySQL

this is how my current table are

tbl_transaction

id_transaction int auto_increment
id_customer int
product (i dont know the type?)
total_transaction varchar(20)

tbl_product

id_product int auto_increment
product_name varchar(40)
price varchar(20)

so when a customer choose one or more item the id_product can be save in product from table tbl_transaction

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
A fish
  • 27
  • 5
  • Sorry but its really not clear what you are asking here – RiggsFolly Jan 05 '23 at 10:45
  • One obvious mistake, a price really ought to be a numeric type and not a varchar – RiggsFolly Jan 05 '23 at 10:46
  • Another probably mistale, `total_transaction` again should be numeric but in fact it probably should **not exist atall**. If you want to know the total value of a transaction, run a query to calculate it, then it will always be accurate. Storing totals in a table allows for the possibility for that total to not be maintained somewhere – RiggsFolly Jan 05 '23 at 10:48
  • I get the feeling you are trying to fight against the proper way of storing data in a relational database. If a sale includes multiple items, store multiple rows, one for each product. – RiggsFolly Jan 05 '23 at 10:50

0 Answers0