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