Junction table with 3 indexes/foreign keys

Is it possible for a junction table to have 3 indexes, linking 3 tables by foreign keys?

The following might illustrate my question a little better.
A user can own 1:N cars.
A user can keep their car at 1:N locations.
A location can be assigned to more than one user.

So Bob might own a porshe, mercedes and a bentley. He keeps the porsche and mercedes in new york, but keeps the bently in london.
Dave owns a ford, which he also keeps in london.

This describes the following table structure:

[B]Owner[/B] (bold indicates table name)
OwnerId[I] (PK)[/I]

CarId [I](PK)[/I]

LocationId[I] (PK)[/I]

And then a junction table that joins the three together:

OwnerId[I] (CPK)[/I]
CarId [I](CPK)[/I]
LocationId [I](CPK)[/I]

Although i think this may violate 3NF, as the case where the same owner has two cars in the same location will cause redundant data to be stored.

So the more traditional method of using 2 separate junction tables seems “cleaner” but more difficult to get data from, eg:



Which approach is best?

second approach is better

but since a car can be in only one location at a time, it’s not a many-to-many relationship and therefore doesn’t require a junction table

and you weren’t clear on whether the same car can be owned by more than one person at a time, but i rather doubt you’d want that, so you don’t need a junction table here either

my advice is no junction tables at all

Thanks for the reply. I see what you mean about a car only being in place at a time, although i did want to maintain a list / history of places the car had been. So over the course of a year or two, i would have a chronological list of locations that car had been kept at. This means that if the location id was added to the car row, updating the location id when the location changed wouldn’t record any history of previous locations.

You are right about the car only being owned by one person. However i can’t see how to not use any junction tables?

okay, so in addition to having location_id inside the car table (the FK to its current location), you’d have a history table consisting of three columns and all 3 would make up the PK

put the owner_id inside the car table (the FK to its current owner)