db desgin question: how to improve the relationship in this schema

[Peter Primrose]

below is a draft-schema of my database; the problem: what happens if the car's owner car is an entity???

As you can see, a car has an owner and the owner can be either person (registration) or an entity (company).

is this the right way to design the tables or is there a better way?

thanks!

[Campbell Ritchie]

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.

See whether that lot is of any help.

CR

[Peter Primrose]

Campbell -thank you for your reply.

*car_id, owner_id, regisrtaion_id, entity_id are all primary key

All columns of each table are irrelevant; there are more columns and I just wanted to be succinct as possible in this question (so you may disregard them)

The core problem I tried to present is the relation between the car and the owner -which can be either an entity or a person (registration) or both.

here's an example:
Mike (reg_id=8) is the owner of car_id=5000
AVIS (entity_id=400) is the owner of car_id=5001

The owner table will look like this:


I'm not sure if this (the owner table) is the right design - having null in a foreign-key column.

???

[Rob Spoor]

I'd switch the car - owner relationship around.

In your model, an owner has a car. Not multiple, but just one. A car on the other hand could possibly have multiple owners.

Now if you turn it around, give the car an owner ID, any owner can have multiple cars, and each car can have 0 (owner_id is null) or 1 owner.

The same goes for registration of course.

[Peter Primrose]

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???

[Campbell Ritchie]

Don't like the idea of permitting null in the registration_id column. If you have a car at all, it ought to have a registration_id too.

And it is difficult to be sure about the cardinalities from the arrows you have drawn, but I think Rob Prime is right about car--->owner.

[Rob Spoor]

Actually, I think I've misunderstood.

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.

[Paul Campbell]

Yes, there is a better way to start this process and you can read more about it here: http://www.orm.net/

If, as someone suggested, you find yourself with a many-to-many relationship... you will need to develop an intersect table in your physical model.

[Peter Primrose]

Thank you all for your interest in my question. I'm embedding an example that might bring more light.

As you can see from the example below:

* Mike Rose is the owner of 2 cars: BMW & TOYOTA.
* The TOYOTA is owned by MIKE ROSE & AVIS (yes, that's possible)
* John Potter is the owner of a JEEP.
* Budget is the owner of Ferrari

So...as Campbell Ritchie noted: 'Don't like the idea of permitting null in the registration_id column.' Correct: how can I avoid that - how would you arrange the tables?

* the owner can be either a person or a company or both; an entity has a tax_id, a person(registration) has a SS.

[Campbell Ritchie]

How about a default 0 for registration_id where 0 is the car dealer?

[Rob Spoor]

That's basically the same as NULL seeing as it never links to a valid person. Even worse, you can't enforce referential integrity anymore.

[Peter Primrose]

What's the resolution? must be some 'design pattern' for such a problem.

[Paul Campbell]

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.

[Campbell Ritchie]

When I said 0 as default, I meant that you put the name of the car dealer as owner no 0. Does that make it any better?

[Peter Primrose]

Paul - if I got you right, you're suggesting to create a virtual-entity named: 'individuals' (say ID 1) and all individuals (eg, Mike Rose) will be associated with that virtual-entity.

so the owner-table will look like this:
owner_id.....car_id.....reg_id....entity_id
100...........1..........50.........1

the 1 on the entity_id will signal that its not an real-entity.

You reason that with

"that would remove your null key and also help you identify the type of owner."

got it!


"If you want to continue to use the tables the way you have them structured,"

this design is just a draft! just wonder if there's a better way?