0

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? :D THX a lot!

6
  • 2
    Think about the relationship a bit more & which direction that relationship goes. Which is the "parent" and which is the "child"? Can clients have multiple contracts? Can contracts have multiple clients?
    – AMtwo
    Commented Nov 19, 2021 at 20:58
  • Well, Clients can have multiple contracts. Contracts cannot have multiple clients, I started with filling out the contracts table first and now I'm having trouble with filling the Client table. I can't see how that helps I am sorry, as I said my knowledge - limited ..
    – Delorik
    Commented Nov 20, 2021 at 8:24
  • You have given the answer yourself: a client can have many contracts (or also no contract) and a contract can only exist for one customer. This means a 0-to-many relationship between clients and contracts. Think about the orientation of the foreign key constraint in cases like that. Surely you must have been told about that in class otherwise you would not be asked to develop an example to illustrate the concept Commented Nov 20, 2021 at 8:28
  • Oh okay.. I'm currently working with 1-to-many so I suppose that's where the issue comes from. I thought that it depends on the certain database as I will not store any clients without contracts I thought the relationship should be 1:M. I surely was told that in class but I'm working in a different field and I've started studying software as a complete beginner a couple months ago so it gets overwhelming at times O:) thank you for your help
    – Delorik
    Commented Nov 20, 2021 at 8:34
  • No, the issue is that you are using foreign keys the wrong way. To express a 1-to-many relationship from a "parent" entity (here your clients) to a "child" entity (here your contracts) means 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 "child" table and points to the "parent" table. Commented Nov 20, 2021 at 11:22

1 Answer 1

2

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.

1
  • thank you very much
    – Delorik
    Commented Nov 21, 2021 at 9:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.