First timer here. Table names are in parentheses.
I am running procurement for multiple laboratories (Labs). Each lab has multiple contracts (Contracts) with multiple vendors (Vendors). Furthermore, each vendor can potentially have multiple contracts with the same lab.
I have the following tables and their respective fields (* designates current primary key):
Contracts
- Contract Number*
- Vendor Names [(multiple vendors in this field(i.e. Vendor1, Vendor2, Vendor3)]
- Lab Names [multiple labs in this field (i.e. City1, City2, City3)]
Labs
- Lab Name*
- Vendor Names [(multiple vendors in this field(i.e. Vendor1, Vendor2, Vendor3)]
- Contract Numbers [multiple contracts in this field (i.e. Contract1, Contract2, Contract3)]
Vendors
- Vendor Name*
- Contract Numbers [multiple contracts in this field (i.e. Contract1, Contract2, Contract3)]
- Lab Names [multiple labs in this field (i.e. City1, City2, City3)]
What is the best way to link the data I have? I would like to avoid having to reformat the fields/tables, however I want to maximize the future fidelity of the database. Specifically, what is the best way to set up the relationship links for maximal efficiency? Many-to-many relationships seem to be the way, but I don't know how to get that to work out for me.
Any help is most greatly appreciated!