Hi, I'm trying to convert a ternary relationship between Crash, Vehicle and Person to binary relationships.

The ternary relationship is as follows:

1 Person in 1 Crash can be only in one vehicle.
1 Person in 1 Vehicle can be only in many crashes.
1 Vehicle in 1 Crash can have many persons.

I think it's call (Crash:Person:Vehicle)=(M:N:1)

I need to not loose any information.
For example I need to know which persons were in Vehicle1 in the Crash1

How can I decompose it to a binary relationship?

Thanks in advance!!! Guillermo.

I'd have three base tables (crashes, vehicles, and people), and one "many-to-many" relationship. The many-to-many relationship links a vehicle and crash to each of the people involved. You need to allow for a NULL vehicle because people can be injured by an accident even if they are not in a vehicle.

-PatP

Sorry I didn't get it....
Which tables would you be linking in a new many-to-many table? I mean, a many-to-many table has the keys of the two other tables...which ones are you suggesting?
Thanks! Guillermo.

I can think of so many possible answers, and I can see that my original answer leaves out some important possibilities.

Why don't you take a stab at what you think is correct, then I'll offer my observations about it. You probably know more about your needs than I do, so you'll almost certainly get closer on the first try than I did.

-PatP

The problem is that each model I came into, seems to be not lossless or not the best one at least...
For example

The ternary relationship (with just one ternary table) could be
Crash Person Vehicle
1 1 1
1 2 1
2 1 1
2 2 1
1 3 2
3 4 1
3 5 1

Then I need a way to make it binary tables

From the theory I have if I call
Crash = X Person = Y Vehicle = Z
(XYZ) = (M:N:1)

Then the two posible decompositions are:
1) (XY)(XZ) with a binary imposition of (X:Z)=(M:1)

And the other one is
2) (XY)(XZ) -or- (XY)(YZ) with binary impositions of
(X:Z)=(M:1) and
(Y:Z)=(M:1)

Using 1)

Crash-Person Crash-Vehicle
1 1 1 1
1 2 2 1
2 1 1 2
2 2 3 1
1 3
3 4
3 5

I don't get what should I do with the binary imposition of (X:Z)=(M:1) here.

Now if I want to get all the people involved in crash 1 that were in the vehicle 1....
And it brings different results between the ternary option and this one...
Please run the attached script (inside the zip) and you'll be able to see what I'm talking about...
Thanks!!! Guillermo.