Database relationship PK table to PK, FK table

Question

Database relationship PK to PK, FK

I have some tables in a database and I am trying to figure out the relationship between them,

Customer: PK CustomerID
Agents: PK AgentID
Listing: PK ListingID
CustAgent: PK, Fk1 CustomerID ; PK, FK2 AgentID ; PK ListingID                               

Are any of these relationships optional?

My question here is what is the crows foot relationship between these tables. (none take nulls so no optionals) I am under the impression that CustomerID in Customer table links to CustAgent with a 1:M, same with AgentID in Agents table links to CustAgent with a 1:M relationship, and ListingID in Listing table links to CustAgent with a 1:1 relationship. Am I correct here?

Answer 1

My question here is what is the crows foot relationship between these tables.

With the premise that, in an ERD, relationship arrows carry two properties on each side: cardinality (one vs many) and optionality (must or must not necessarily have instances/records on that side).

By what I read from the schema you have presented:

CustomerID in Customer table links to CustAgent with a 1:M,

No, the other way round: CustomerID in CustAgent refers to ID in Customer, so is an FK from CustAgent to Customer.

That said, out of the box with a non-nullable FK you indeed get a 1:M relationship that is mandatory on the side of the parent table (Customer here), and optional on the side of the child table (CustAgent).

Informally that reads: every Customer can "have" zero or more CustAgents, every CustAgent must "be had by" one and only one Customer.

AgentID in Agents table links to CustAgent with a 1:M relationship,

No but yes, as above.

ListingID in Listing table links to CustAgent with a 1:1 relationship

No. First of all, there is no FK from CustAgent to Listing nor from Listing to CustAgent (in the schema you have presented), so at the moment there is no relationship at all between those two tables. I'd guess here, similarly to above, it is an FK on ListingID of CustAgent that is missing.

But, even that fixed, you'd still get a 1:M relationship out of the box. To make it into a 1:1 relationtionship, you'd need a uniqueness constraint on ListingID of CustAgent, i.e. on the FK.