So I have a page website where a HQ user can fill out a form to give details about one of its 25 locations around the country (called Units). There is also a page where they HQ user can fill out a form to give the details of one of the 147 Suppliers that may or may not supply some of the Units.
My question is what would be the best way to link up the Units with the Suppliers in a way that is easy and makes sense?
Obviously I could set up a form where you select a single Unit and single Supplier, but this would be tedious and time-consuming for the HQ user as some of the Units might be supplied by 50+ Suppliers. Is there another smarter way to go about this?
either have a seperate table that just connects the two
unit_suppliers
unit_id,supplier_id
with the primary key being a combination of unit_id and supplier_id
or have a suppliers field in the units table that contains a comma separated string of the supplier ids for that unit
For the first case, to get suppliers for a unit the sql would look like
SELECT * FROM suppliers WHERE id IN (SELECT supplier_id FROM unit_suppliers WHERE unit_id='$UnitID')
($UnitID being a php variable containing the units id)
For the second you would do
SELECT * FROM suppliers WHERE find_in_set(id,(SELECT suppliers FROM units WHERE id='$UnitID')) <> 0
Then in your form have some way of selecting multiple suppliers for a unit and insert into the database accordingly