0

I am creating app with PHP that uses Mysql DB to manage data. Tables are like this:

user         purchasing      medicine
____         ________        ________
id_user      id_user         id_medicine
username     id_medic        item

What really bogus me.. should insert values in purchasing(junction table) after i update user, and medicine table? Should I use transactions for it?

And second question is, are id_user and id_medic in junction table, indexes, or unique?

Sorry, Ive forgot a mysql. Thanks in advance

fugitive
  • 357
  • 2
  • 8
  • 26
  • At this point your asking for a solution, but you're not providing the business rules you want to apply i.e. We cannot suggest indexes or unique if we don't know what you require. – davejal Dec 23 '15 at 14:36
  • yes, you must first insert values in user and medicine before you insert to purchasing. otherwise, you won't know id_user or id_medic. regarding transactions, that depends on several things... transactions are a safety net. if, for example, you need to guarantee that a user isn't inserted without also having a record in purchasing, then yes, you will want to use transactions. – devlin carnate Dec 23 '15 at 15:29

1 Answers1

0

  1. You should insert to the medicine table first. Because, if you are setting the database properly, the purchasing.id_medic should refer to medicine.id_medicine, So, inserting into purchasing table should fail if there is no such id_medicine added before. (And yes, you should use transactions, if you think there is possibility to rollback after inserting some rows).

  2. In purchasing table, id_user and id_medic both should be indexes for sure, but whether it should be unique it depends on your use cases. For example :

    • If you think any user can purchase only one item, then id_user should be unique.

    • If you think any item cannot be sold more than once, id_medic should be unique.

    • If you think any user can buy any item only once, there should be a unique constraint with both id_user and id_medic

Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ
  • 4,151
  • 1
  • 19
  • 54