Expand all | Collapse all

Do I need a Many to Many relationship?

  • 1.  Do I need a Many to Many relationship?

    Posted 09-22-2017 15:07
    My company is having an integration developed between our Booking Software and Quickbase to aggregate our bookings from all of our stores in one place. The data that is associated with this booking is being mapped to three different tables. The tables are as follows:

    1. Booking Info - The data being mapped here is general booking information (number of participants, type of booking, location, etc.)
    2. Booking Transaction: The data being mapped here is anything financial associated with the booking (revenue, taxes, coupon discounts,etc)
    3. Booking Customers: The data being mapped here is the customer info associated with the booking (Customer Name, Email, etc)
    I want these to all be linked. On the booking info table, I want there to be a report link for Booking Transactions for that booking and a report link for customers related to that booking. I've figured out how to do this with Booking Transactions because I'm just going to bring down the key field from our booking software into both tables. This being said, I can't figure out the best way to link my customers because over the course of time, a customer might have many bookings. I want the customer to be linked to this booking, but I want a customer to be able to have many bookings over the course of time. Does this make sense? I'm happy to clear this up.

  • 2.  RE: Do I need a Many to Many relationship?

    Posted 09-22-2017 16:11
    The relationships that you will need are as follows:

    Customers -> (has many) Bookings
    Customers -> Transactions
    Bookings -> Transactions

    The person writing the Integration should push all the data to the Bookings table and then have either webhooks or other custom code to associate the booking with a customer (or create a new customer).  

    The kicker on this is to identify a unique identifier for the 'customer'.  Which is most likely the email address.

    Honestly if you have somebody writing the integration, they should know this.  If they don't, I'd be weary of their code as well.