I'm trying to think this out and hope someone can help. I'm building a new database and I have Providers, Addresses, Solicitations, Contracts, Maintenance, andTerminations. The provider can have multiple addresses and then multiple, solicitations, contracts, maintenance and terminations for that address. So the provider is a many but so is the address. I'm trying to figure out how to set up the relationships, so that the solicitations, contracts, maintenance and terminations are related to a provider at a specific location. I want to keep the database so there is only one record for provider and one record for an address.
Thanks,
Question
Table to Table relationships where there is a many to many
+12To form a many to many between two tables, you need a third Details table:
Provider >> has many >> Details Records.
Address >> has many >> Details Records.
The Details table collects all the other information like terms and maintenance and contracts, etc. This arrangement prevents redundancy among Provider and Address, and gives you the many-to-many relationship between Provider and Address you are seeking.
The two parent records are pulled in to the Details Record via drop down selection lists or Record Pickers.
Provider >> has many >> Details Records.
Address >> has many >> Details Records.
The Details table collects all the other information like terms and maintenance and contracts, etc. This arrangement prevents redundancy among Provider and Address, and gives you the many-to-many relationship between Provider and Address you are seeking.
The two parent records are pulled in to the Details Record via drop down selection lists or Record Pickers.
+12Okay I was looking into this but wasn't sure if that was the correct direction. So the other detail tables should have a one to many relationship to this new table correct?
You can place standard multi-select lists in the Details table for solicitation, contracts, maintenance, or, you can mimic the model of Address >> has many >> Details and Provider >> has many >> Details. You can do Contractor >> has many >> Details, and Maintenance >> has Many Details.
The object is to make your unique data stay unique. If you need to do Provider maintenance, that is done in the Provider table; Address maintenance, the Address table. Actual work orders would be created in the Details table.
The object is to make your unique data stay unique. If you need to do Provider maintenance, that is done in the Provider table; Address maintenance, the Address table. Actual work orders would be created in the Details table.
+12I don't think I did this correctly or maybe this is not what I'm looking for. I want it so that a details table and for this example, let' say a solicitation is connected to a provider at a certain location. I have a table with providers and a separate table with locations. I'm trying not to have come combine the tables together. Is this the way I should handle this?
You can have those two in separate tables, but there has to be a way to tie a Provider to a Solicitation.
It seems like you may be describing dependent drop-down lists. Where what you select in box 1 determines what is selectable in box 2.
Such a relationship would look like this:
Solicitation >> has many >> Providers ( if that is the correct direction )
Solicitation >> has many >> Details
Providers >> has many >> Details.
In the Details table you will have Solicitation as as drop down list, and Providers as a drop down list. If my above relation is correct, you'd select a Solicitation, then the Provider list would customize its selections according to the selected Solicitation.
Do you know how to set up dependent drop-down lists?
It seems like you may be describing dependent drop-down lists. Where what you select in box 1 determines what is selectable in box 2.
Such a relationship would look like this:
Solicitation >> has many >> Providers ( if that is the correct direction )
Solicitation >> has many >> Details
Providers >> has many >> Details.
In the Details table you will have Solicitation as as drop down list, and Providers as a drop down list. If my above relation is correct, you'd select a Solicitation, then the Provider list would customize its selections according to the selected Solicitation.
Do you know how to set up dependent drop-down lists?
+12Yes I do know about the drop down list. This is what my tables would look like.
Provider>>has many>>address
Address>>has many>> Providers
Solicitation>> has one provider many addresses for that provider
Excellent. And from there build the work order in the Details table and all parent data stays unique and manageable.
Sign up
Already have an account? Login
Login to the community
Login with Quickbase US – quickbase.com Login with Quickbase EU – quickbase.eu
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.