User has Areas, which have Buildings, 3 PK's or tables in between?

Question

I'm building a database which has the following

• User
• Area
• Building

A User has many areas, and a user can add buildings to his/her areas.

I can up with the following idea but I feel like there's some duplication going on

user (id, name)
user_area (user_id, area_id)
area (id, size)
area_building (area_id, building_id, user_id)

Since this allows an area_building that is put on an area that doesn't belong to the user.

Would it be better to have this layout?

user (id, name)
user_area (id, user_id, area_id) <-----
area (id, size)
user_area_building (user_area_id, building_id) <-----

A user can have an area and build on it, another user can also have that area and build on it, but they won't ever see each other's building.

Answer 1

From the question and comments:

A user can have an area and build on it, another user can also have that area and build on it, but they won't ever see each other's building.

Based on this, both solution 1 and 2 are ok.

The user_area table won't be a many-to-many table then, but become it's own entity.

Yes, it would a many-to-many table and an entity of its own. That's only a matter of definition really. If you have additional attributes (eg inventory per user and area), then the columns can be added in the user_area table. Then the table is more obviously an entity of its own but it is still a many-to-many table.

Let's say that later a user can also have an inventory for a certain area of his, and there can be payments added to a certain area, would solution one still be okay? Each of these new tables will have a reference to both user_id and area_id.

The payments table would need to be separate and have a FK referencing user_area.
If it were payments associated to user and building, it would be a separate table with a FK referencing building.

A user has many areas, and a user can add buildings to his/her areas.

Based on this, the area_building from the 1st solution looks correct but with a FK on (area_id, building_id) to user_area

area_building (area_id, building_id, user_id)

So my suggestion would be to use solution 1 with the following FKs:

user (id, name)
area (id, size)
user_area (user_id, area_id)
building (area_id, building_id, user_id)

I would also just rename it to building and have PK the (building_id). I would also get rid of the id names, in favour of more descriptive id names but that's a personal preference.

In detail:

user
    user_id    PK
    name
area 
    area_id    PK
    size
user_area
    user_id    PK  FK1 -> user (user_id) 
    area_id    PK  FK2 -> area (area_id)
building 
    user_id         FK3 -> user_area (user_id, area_id)
    area_id         FK3 
    building_id  PK

Answer 2

These schemes are not equivalent, so one or the other is incorrect.

The first scheme is potentially contradictory. Nothing prevents adding a (user_id, area_id) values pair into area_building, which is not listed in user_area. If you fix it by composite foreign key then user_area become unnecessary.

The user_area table won't be a many-to-many table then, but become it's own entity.

In the described scheme the pair (user_id, area_id) acts as an entity.