Forum Discussion

SallyAndreatta's avatar
SallyAndreatta
Qrew Member
2 years ago

referencing record ID #s in related tables

You will love this one.

Trying to build an app that tracks vendor (partner) contracts which include services, number of units by service and pricing by service, then be able to have partners report on that each month so that we can reimburse them.

So...I asked Chat GTP to write that for me.   What I can't figure out is how to get the related Record ID # (which I assume is the ID referenced below for each table. For example Contract_ID or Service_ID) is to the related tables so that they tie together.  I have created the relationships but they definitely are not connecting by themselves.

Here is what I got back:

To model this relationship in a relational database, you can create the following tables:

  1. Vendors: stores information about vendors, including vendor id, name, address, and any other relevant details.
  2. Services: stores information about services offered by vendors, including service id, name, description, and any other relevant details.
  3. Contracts: stores information about contracts between vendors and customers, including contract id, vendor id (foreign key to Vendors table), customer id, start date, and end date.
  4. Service_prices: stores the price of each service for a particular contract, including contract id (foreign key to Contracts table), service id (foreign key to Services table), price, and any other relevant details.
  5. Service_units: stores the units of each service for a particular contract, including contract id (foreign key to Contracts table), service id (foreign key to Services table), units, and any other relevant details.

Here's an example of how each table might look with some sample data:

Vendors:

vendor_id name address
1 Acme Inc. 123 Main St.
2 XYZ Corp. 456 Market St.

Services:

service_id name description
1 Website design Design and build a website
2 SEO Search engine optimization services
3 Social media Social media management services

Contracts:

contract_id vendor_id customer_id start_date end_date
1 1 101 2021-01-01 2022-01-01
2 1 102 2021-03-01 2022-03-01
3 2 103 2021-04-01 2022-04-01

Service_prices:

contract_id service_id price
1 1 1000
1 2 500
2 1 1500
2 3 1000
3 2 750

Service_units:

contract_id service_id units
1 1 10
1 2 5
2 1 15
2 3 10
3 2 7

To track the monthly input of this, here is what was returned:

This table would have the following fields:

  1. contract_id: the id of the contract (foreign key to Contracts table).
  2. service_id: the id of the service (foreign key to Services table).
  3. month: the month for which the units are being tracked.
  4. units: the number of units of the service used during the month.

Here is a summary of what the relationships would look like between tables:

  1. Vendors to Services: one to many. A vendor can have many services, but each service belongs to only one vendor.
  2. Contracts to Services: many to many. A contract can have many services, and a service can be part of many contracts. This relationship is represented by the Service_prices and Service_units tables, which contain a foreign key to the Contracts and Services tables.
  3. Contracts to Service_prices: one to many. A contract can have many service prices, but each service price belongs to only one contract.
  4. Contracts to Service_units: one to many. A contract can have many service units, but each service unit belongs to only one contract.
  5. Services to Service_prices: one to many. A service can have many prices (through the Service_prices table), but each price belongs to only one service.
  6. Services to Service_units: one to many. A service can have many units (through the Service_units table), but each unit belongs to only one service.
  7. Service_units to Service_units_by_month: one to many. A set of service units can be divided into many units by month (through the Service_units_by_month table), but each unit by month belongs to only one set of service units.






------------------------------
Sally Andreatta
------------------------------

2 Replies

    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      The short answer is that if the data is being entered manually, the ADD Child button will populate the Related Parent ID automatically as Quickbase will build the button to do that when you make the relationship.

      However if the data is going to be imported then you will need to have a custom Key field other than the Record ID  on any of your Parent tables and the import source will need to know the Key field of the Parent in order that the children attach to the Parents.

      ------------------------------
      Mark Shnier (Your Quickbase Coach)
      mark.shnier@gmail.com
      ------------------------------