Storing multiple contracts under one clients name SQL

Posted on

Question :

I’m working on a school project, very simple database so my knowledge is very limited.enter image description here

Klient = Client
Smlouva = Contract

The database basically just keeps track of approx. 50 contracts, 35 clients and 6 insurance companies with different types of contracts.
I’m trying to assign multiple contracts to one client. ID_smlouva acts as a FK to the Client table.

insert into Klient
VALUES (1, 989, ‘John’, ‘McDavid’, 23, 1)
(1, 989, ‘John’, ‘McDavid’, 23, 2)
doesn’t work because I’m simply duplicating PK.

Any other simple way to do this as I haven’t been able to understand any other tips about this topic out there? 😀
THX a lot!

Answer :

Let’s summarize the issue in your own words: “Clients can have multiple contracts. Contracts cannot have multiple clients”

This means a 1-to-many relationship between clients and contracts. Expressing a 1-to-many relationship from a “parent” entity (here your clients) to a “child” entity (here your contracts) in a relational database is done via referential integrity, i.e by using primary and foreign keys.

It means that the foreign key goes from the “child” to the “parent“. I.e. your contracts table (the “child”) needs a column that contains the id of the client that contract belongs to: the foreign key is on the contracts table and points to the clients table.

As for the distinction between 0-to-many (like above) vs 1-to-many, it is rather important. In real life the parent/child relationships are pretty much always 0-to-many: meaning that a parent can exist without any children. This is the case for your example when using a plain foreign key: a client can exist without any contract. It will be the case at least for a short period when the customer first registers, then later on starts opening contracts. During that period, the parent (client) exists without any child (contract). That is pretty much always the case in real world applications.

Enforcing a strict 1-to-many is difficult. It will require additional logic to make sure that no ever client exists without any contract. That can be done via triggers but raises all sorts of questions:

  • How can I register a new client (= insert a row in the client table) since at that time, no contract exists yet. And vice versa, how can I insert a contract if the client does not yet exist ? Most databases solve this by deferring the verification of constraints until the end of the transaction: you can then insert the client and a contract, then commit and integrity will be checked at that point.

  • What happens when I delete the last contract for a client ? Should that imply the automatic deletion of the client ? Or should it be prevented since a client must have at least one contract ? Both have painful consequences and are not trivial to implement.

Then you have insurance companies. This presents an additional challenge. You have essentially two options depending on whether a client is allowed to own contracts with multiple companies, or whether a client is with one insurance company only.

  • In case #1 (a client is allowed to own contracts with multiple companies), then there is a 1-to-many relationship (actually again 0-to-many for ease of use) between insurance companies and contracts. Which means a foreign key from contracts to companies

  • In case #2 (a client is with one insurance company only), then that means that all contracts for that client belong to the same insurance company. That is then better represented by a 1-to-many between companies and clients. Which therefore means a foreign key from clients to companies this time.

At this point, it is a good idea for you to re-read the specification you have been given, especially to understand the relationship between companies and contract / customers. Misunderstanding those things is a common cause for implementing wrong designs that do not meed the application requirements. Any ambiguity must be lifted at the very beginning.

Leave a Reply

Your email address will not be published. Required fields are marked *