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:
Also, a service involves parts, labor, and consumable.
How would you model this? As a separate entity? Or in the service
entity or part of the relation (intersection entity) between car and
service ?
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 between SERVICE
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.
I want to call attention to one of your business rules: Representative is Customer and is Company.
This sounds like a superclass/subclass situation to me, and I think you have the EER diagram well in hand, as far as ER modeling goes. Where things start to get interesting (and a little messy) is when you try to switch over from an ER model (which is abstract and implementation neutral) to a relational model (which is not).
At this point, you'll have to choose between a few well known techniques that make up for the fact that the relational model doesn't incorporate inheritance. You may wish to include this tag in your original question: subtypes
Here are three of the techniques in a nutshell: single table inheritance, class table inheritance, and shared primary key.
Single table inheritance lumps all the subclasses together in one table. Data that does not pertain to a given instance is left NULL.
Class table inheritance has one table for the superclass and one table for each subclass. Generalized data goes in the superclass table, and specialized data goes in the appropriate subclass table.
Shared primary key enforces the one to one nature of the relationships between each subclass and the superclass. It also allows a foreign key elsewhere in the database to reference the superclass without knowing or caring which subclass it belongs to.
For a good treatment of this subject, look up Martin Fowler's treatment on the web.
Edit based on feedback
The question of which ER model is "correct" is usually determined by the database design team by analyzing the subject matter, and coming up with a model that depicts the way it works in "the real world". This presumes that the database is yet to be designed, but the business itself has already been designed, and the business design is not to be changed. Your comment makes me think that you are designing (or redesigning) the business itself, not just the database. If so, then the question is outside the expertise of database design.
To deal more specifically with the questions you asked:
- Is supertype / subtype needed here (since subtype Entities have no unique attributes and since it gives me a headaches)
If there are no attributes that are specialized to the subtypes , then no inheritance (from a data perspective) is needed. So it won't make any difference to the relational design. Don't give yourself headaches. Pick a model that works for your business, and let it go.
- How to implement inheritance with one entity in recursive relationship - if for instance Customer / Representative is in recursive relationship (relational table example needed)
If Customer and Representative are the same entity, give them a common name. One entity with two names is confusing. If they are different entities, rephrase your question. From my answer to question 1, why is implementing inheritance called for?
Are "recursive relationship" and "reflexive relationship" the same thing? If not, please clarify your question.
- Can you give me relational solution (EER to relational) with some examples (like table with data) so I can wrap my mind around it.
That's what I thought I was answering the first time. Fowler's treatment of inheritance/relational issues is available on the web. Here and here.
His examples are about players and cricketers, not customers and representatives, but the issues concerning implementing inheritance (really pseudo inheritance) in a relational system are not case dependent.
Best Answer
Depends if a different admin or a different user could join in on the followup conversation, other than those who started it in the originating Complaint.
If a complaint's message thread is forever tied to the single, originating user, then both the connection (foreign key) and idUser column should be excised from ComplaintMessages. Same with the admin if it's fixed, and there's no chance a different admin might have to answer the followups (this side might be less true).
Consider if it even makes sense to have this many separate tables. Most would keep all users, regular and admin alike, in the same table, and have a field - or a connected rights table - to determine who has what permissions.
Similarly, there doesn't seem to be much reason to keep Complaint and ComplaintMessages separate. Considering it sounds like there could be multiple followup messages, you are currently missing some kind of mechanism to keep the followup messages in chronological order (other than possibly relying on the primary key id field providing that ordering). Instead of the two, you could have a single Messages table, with a "thread ID" field added, referencing the first entry in the same table. And probably a date/timestamp field to keep everything in order, or a reference back to the previous message's ID.