I am working on a project where Businesses can make accounts, then these businesses can have a number of representatives. The representatives will be sending offers to other businesses within the system. Offers can be sent to multiple businesses at once, and each business should have the ability to choose from a drop down list if they want to accept\deny\put on hold the offers they receive. I am working with PHP and so far I created everything and it's working but. When a representative who's working for business C sends an offer to businesses A and B, A and B should be able to view the offer and change their status on the offer if they wanna accept\deny\put on hold. How should I structure the database to make this possible and efficient? I currently have a table for "Businesses" A table for "Business details" A table for "Employees" a table for "offers". When an employee sends an offer to any business\es, these business\es IDs are stored in the "Offer's" table as a string and I'm retrieving the offer's details to whoever business that has their ID within that string in the "offers" table. But this way, multiple businesses can VIEW the offer but when they want to change their own stand alone status about the offer I get the issue which I am asking for help with.
Maybe on business creation I should make an extra query like 'Create table' . $business_name . '_offers' ;
so every time a business is created there's a table for their offers is created but how can I define the fields for the newly created table?
Please note that I also need to be sending notifications about the offers' status changes later on. I have never done this and any help with the queries\structure would be appreciated. Sorry for the long post. Thank you!