Discussions

Expand all | Collapse all

Table to Table relationships where there is a many to many

  • 1.  Table to Table relationships where there is a many to many

    Posted 08-22-2018 16:16
    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,


  • 2.  RE: Table to Table relationships where there is a many to many

    Posted 08-22-2018 16:28
    To 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.  


  • 3.  RE: Table to Table relationships where there is a many to many

    Posted 08-22-2018 16:59

    Okay 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?


  • 4.  RE: Table to Table relationships where there is a many to many

    Posted 08-22-2018 17:17
    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. 


  • 5.  RE: Table to Table relationships where there is a many to many

    Posted 08-22-2018 17:18

    I 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?


  • 6.  RE: Table to Table relationships where there is a many to many

    Posted 08-22-2018 17:25
    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? 


  • 7.  RE: Table to Table relationships where there is a many to many

    Posted 08-22-2018 18:03

    Yes 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




  • 8.  RE: Table to Table relationships where there is a many to many

    Posted 08-22-2018 18:33
    Excellent. And from there build the work order in the Details table and all parent data stays unique and manageable.