Try putting some cardinalities on those arrows. Put them on in the format 0..1 or 1..n. See whether you get any arrows which might have more than one at both ends. Any >1 to >1 cardinalities must be got rid of, but I think you probably don't have any.
You haven't underlined or otherwise marked your keys.
Some of the fields seem odd; "loan" and "amount" are not usually fields of a "car," whereas "make" "colour" or "registration number" would be. You could have "registration number" as a PK in "car." "Loan" and "amount" look as if they ought to be a table or tables of their own. Are you going to allow two loans for 1 car or one loan for two cars, in which case you may have a many..many cardinality?
The "owner" can be an individual or a body corporate, which can both (in English law) be a "person" for the purpose of owning a car. You would need columns like name address status (company/individual). Consider using "registration" as a foreign key, or having an "owner number" as PK and using that as an FK in "registration."
For "registration" you only need things like key for car, key for owner, date, etc.
Hey Rob, First, thanks for your reply. Second, you wrote:
In your model, an owner has a car. Not multiple, but just one.
Incorrect. The owner table can have more then one owner (registeration_id) so you can have someone with a registration_id (eg 8) with *N* cars associated to him (owner is just a relationship between the car(S) and the Person (registration) OR entity.
Also, you solution doesn't consider an 'entity'. So if the car's owner is an entity - how would you describe it in the car's table???
Where are the actual owners stored? In the owner table, or the registration table? Because from the last reply, I deduce that the owner table does not actually hold the owner itself (and therefore is incorrectly named).
I don't know how your local rules are, but in my country, every car has only one owner. A car cannot have no owner (unless it's fresh from the factory and not sold yet, but even then the dealer could be considered the owner). Therefore, the owner could be considered a foreign field of the car table.
Now if your owner table does not include the actual owner but the registration instead, then that should be a foreign field of the car (as a car can have only one registration), and the owner should be a foreign field of the registration (which it is if you swap table names).
Now you can also figure that each registration has one car (which is also true). This is, IMHO, a case of a 1 to 1 relation - a registration can be for only one car, and the car can have only one registration. In most cases, the tables can be merged into one, containing fields of both tables.
As for the entity table, perhaps you should include this in the owner table. A tax number is a property of an owner (person or company), not of the car registration.
Originally posted by Peter Primrose: What's the resolution? must be some 'design pattern' for such a problem.
But isn't your "entity" just another name for a type of owner and attribute of that owner is that it is a corporation instead of an individual? You are more or less using your owner table to try to resolve a many-to-many relationship between owners and cars. If you want to continue to use the tables the way you have them structured, you could create another entity called individual and that would remove your null key and also help you identify the type of owner. Of course, after you do this you may discover that you can move your owner attributes out of the owner/entity/car intersect table into a separate table.