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.
Best Answer
The General Advice:
When you are starting off learning how to model databases, one of the most important rules of thumb is: Every tangible thing that matters to your system is probably an entity type.
This is a really good place to start with any logical database design. If you spend some time up front thinking about what kind of things matter to your system, then you're going to come up with a solid foundation on which to build your system. The things your organization cares about will change much less frequently than the business processes and rules your organization uses to deal with those things. That is why a solid data model is so important.
Another important rule of thumb is: Normalize your data model by default and only denormalize when you have a (really) good reason to. This is especially true for a transactional system. Reporting systems and data warehouses are a different story.
The Specific Answers:
Cardinality: If you think about it, it is easily the case that a car could have never been serviced (by your shop). Therefore a minimum cardinality of zero is very plausible. On the other hand, by the time the vehicle matters to your system it may well be because it has had its first service - so a minimum cardinality of one is also plausible. You need to think about what the business rule is for your organization and model accordingly. I would think, for example, that a car dealership would have lots of cars in its system that haven't been serviced by the dealership yet, whereas a muffler shop wouldn't care about cars it hasn't serviced.
Service Items: You asked:
Let's consider an intersection entity between car and service... You could potentially use such an intersection to store details about the service, like how much labour, which parts, and consumables were used.
However, using an intersection implies a many-to-many between cars and services, but you've already stated that each service is for (exactly?) one car. Using an intersection entity to track service item details would mean your model isn't properly normalized.
Consider this model as an alternative:
In this model each service is for one vehicle, but each service can have many instances of labour, parts and consumables. This model follows the first rule of thumb I mentioned and makes an entity type out of each tangible thing the system cares about. This might be a good first stab at a logical model.
One of the issues with the above model is that it doesn't handle one aspect of how your system is likely to want to use the data, at least not very well. One of the most important reasons for tracking all of this data in your system at all is so that you can print off an itemized service invoice. That means that a service line item is itself a thing which is important to your system. If you take that into consideration, you might end up with something more like this:
Notice in this second alternative
SERVICE_LINE_ITEM
is recognized as an entity type. It is an intersection betweenSERVICE
and the generic line item type:SKU
. A SKU is a supertype entity that could be a part, a consumable or some kind of labour. You don't need to have a logical supertype for service line item types, but a lot of systems would be modeled this way because it makes the transactional detail much simpler.This second model introduces abstract entities over and above the concrete entities of the first model. Introduction of abstractions like this is one of the things that tends to happen as you move from an initial logical model, based mostly on tangible things to a physical model.
As you gain experience with data modeling, you'll get good instincts for moving past the conceptual/logical model stage directly to a well structured physical model.