Extracting entity from attribute in entity-relationship diagram

Question

I am developing a database model where I need to capture information about rent contracts for parking places within a parking. I have thus defined an entity called PARKING CONTRACT.

Requirements (r.) say, that each PARKING CONTRACT should be signed for exactly 1 car, which is, in turn, owned by exactly 1 car owner.

To fully define a PARKING CONTRACT, r. say, I need to record various information about car owner and his car. For each car owner: full name, address, passport number, etc. For each car: technical passport number, registration number, brand, etc.

According to the r. (they do not go into such details, here I am judging on the basis of attributes that need to be defined!), a car owner can be uniquely identified by their passport number; a car can be identified by registration number.

I first thought it would be good to model CAR OWNER and CAR as separate entities, and came to this:

.-----------------.                      .-------.                 .------------.
| PARKING CONTRACT|     Is signed for    |  CAR  |   Is owned by   |  CAR OWNER |
|                 |O_____________________|       |O________________|            |
'-----------------' 1                   1'-------' 1..*           1'------------'

But then I realized that with this design we may store as much CAR OWNERs as we like without any of them having any PARKING CONTRACTs for any of their CARs. I think it is not good - why a database should be able to store information about people which never had any PARKING CONTRACTs.

Another option I see here is to not extract CARs and CAR OWNERs into separate entities and to keep all information about them in the PARKING CONTRACT table, for each PARKING CONTRACT.

But this option doesn't seem to be too nice as well, as the table for PARKING CONTRACT, imho, becomes a chimera which tries to handle too much different information.

Also, I have read this brochure: https://support.ca.com/cadocs/0/CA%20ERwin%20Data%20Modeler%20r8-ENU/Bookshelf_Files/PDF/ERwin_Methods.pdf

and on the page 38, it tells that when you end up with attribute names like parking-contract-signer-passport-number, then that is a sign of a design problem.

The question is: What is the commonest way to handle situations like this one? Is any of these two approaches preferred? Or, maybe, there's a whole different third approach? And why is that approach preferred? Thanks!

Answer 1

Unless you have a good reason not to, you should normalize your database schema. When you normalize your database schema you are avoiding potential issues with data consistency that can occur when you have duplicated data.

Google the terms: insert anomaly, update anomaly, deletion anomaly. You will see lots of examples of the kinds of problems that an unnormalized database can cause.

Regarding modeling CONTRACT separately from CAR: I have found that one of the best places to start with a data model is to treat each tangible (or significant intangible) thing that your system cares about as its own table.

The hard reality is that business rules change, but the things that your business cares about don't change - at least not at nearly the same pace. Therefore, you want your data to reflect the reality of the things your business cares about.

People are not contracts. Sure, today your boss says one contract per person per car. What happens when your boss buys (or builds) a new parking garage and that car has a contract for both? What happens when one person sells a car that has a contract? The contract is signed by a person, not by the car? Does the contract go with the car or does it stay with the owner? What happens when your boss tells you he wants to start tracking future-dated contracts so that a car can have both its current contract and its contract for next year?

You don't have to normalize. You could try to get away with one big table for everything. You could just write everything down in a spreadsheet or on a piece of paper. However, if you want to build a system that is flexible for the future, then start by normalizing.

---

Regarding IDs for your tables: Never use anything external as the ID for something, unless you are totally positive that it will never change. A vehicle VIN is OK as an identifier. Nothing causes a VIN to change and it is guaranteed to be unique. A passport number is a terrible ID because (a) it will definitely change as time goes on and (b) you can't be sure that it will be unique. When primary keys change it causes all kinds of headaches. This is why a lot of people assign internal, meaningless surrogate keys to their tables.

---

EDIT: Something Else to Consider:

Something else you should consider is that you should NOT try to model (and maintain data for) things which aren't important to your system.

That is just making work for yourself now and down the road. Your system will be more complicated to build, maintain, upgrade and use.

Consider the following ERD:

The relationship between CAR and OWNER is not important to your system! At first this seems a little bit counterintuitive, but think about this: Does your boss care who actually owns a car? No! What they care about is who (PERSON) signed a contract to pay to have which CAR parked in his garage. Therefore the relationships that are actually important are between CONTRACT and CAR, and CONTRACT and OWNER. The relationship between CAR and OWNER is not directly significant to your system, unless you have a business rule somewhere that says something about how contracts stay with cars, not owners when people sell their cars. That is probably dicey from a legal perspective.

Answer 2

Based on your model, it looks like its unlikely that more than one car is owned by an owner (I guess at most two, if the car owners are private individuals), and I guess a parking contract cannot exist for more than one car (these are some assumption I am making, please correct me if I am wrong).. In that case just one table with all the columns will do..

A few rules that I would like to mention are: If you see a use case where a car_owner or a car can be reused elsewhere (maybe in a duration table to determine the timings of parking), then splitting the tables to normalize makes sense, if there is only one instance of a car\car_owner throughout the data model for a unique tuple in all cases, using a single table is fine.

To optimize storage you can define subtables for values that repeat frequently (like say the Car_Make). However this should bot be done for small data modes.

1. Since one car may not have more than one parking contract, it is fine to have CAR as a part of the PARKING CONTRACT TABLE

   CREATE TABLE PARKINGCONT_TBL(contract_id,,car_regnumber,,owner_id [FOR_KEY TO OWNER_TBL]

   CREATE TABLE OWNER_TBL(owner_id,)

2. The question of adding a customer without a contract should not arise because whatever is inserting (the application) should create a contract (not a customer), so a owner is added only when a contract is created (there is no way to enforce this, since there is no dependency between owner and contract, and creating one will result in a cyclical dependency)