0

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.

1
  • Your tables imply "many-to-many"; your words imply "one-to-many". Which does your data model need?
    – Rick James
    May 7 at 21:46
2

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
7
  • Interesting! I'm wondering, 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. Shouldn't they be merged then such as proposed by @Akina in the comments? May 6 at 9:57
  • If the inventory is per user and area, the attributes/columns can be added in the user_area table, no need for separate table. May 6 at 9:59
  • I see what you mean, but for payments this could be many rows of payments for a certain area_id, user_id May 6 at 10:02
  • Yes, see my edit. The payments would be a separate table. May 6 at 10:05
  • 1
    No, one FK referencing the PK of user_area. If the PK is composite as my suggestion, then a composite FK. If it is single-column (by adding user_area_id as PK) then a single-column FK. The user_area would be an entity of its own in either case. May 6 at 10:57
0

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.

1
  • How do you mean "user_area" becomes unnecessary? May 6 at 10:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.