0

(Edited 6/4)

I am designing a database that contains the following tables about hotels and their locations (in the context of this question let's say hotel names are unique):

[Hotel]    
HotelId PK
HotelName AK

[HotelLocation]
HotelId FK
HotelLocationName

(HotelId, HotelLocationName) is the PK in HotelLocation. There cannot exist two HotelLocations in the same Hotel with the same HotelLocationName.

Some sample data could be:

| Hotelid | HotelName      |         | HotelId | HotelLocationName |
----------------------------         -------------------------------
| 1       | Holiday Inn(1) |         | 1       | Reception         |
| 2       | Four Seasons   |         | 1       | Pool              |
| 3       | Holiday Inn(2) |         | 2       | Reception         |
                                     | 2       | Dinning room      |
                                     | 3       | Room 100          |
                                     | 3       | Room 101          |

It is required that both HotelName and HotelLocationName are editable.

For that reason, in the case of the Hotel table I use a generated immutable HotelId and keep the HotelName as an alternate key (AK) with a unique constraint.

I could do the same for the HotelLocation table and change it like:

[HotelLocation]
HotelLocationId PK
HotelId
HotelLocationName

where (HotelId, HotelLocationName) is an AK. The problem is that I have a lot more tables related with it, where an FK like (HotelId, HotelLocationName) gives me a direct relation to the Hotel that i don't want to lose.

I know I can still use a similar FK to HotelLocation's AK but the problem with cascading updates remains.

I though about generating the HotelLocationId in a way that i can have a (HotelId, HotelLocationId) PK, for example taking the MAX(HotelLocationId) + 1 for a specific HotelId for every new record, but I would prefer an alternative solution if there is one.

Is there any common way to deal with this kind of situation?

Giannis Tsim
  • 96
  • 8
  • 2
    The common way is don't make mutable fields part of your table key, but we're past that. How often do hotels change their name? Perhaps, you just live with expensive hotel name updates. Also, How is each hotel name unique? Aren't there hundreds of Holiday Inns? – Gilbert Le Blanc Mar 18 '20 at 19:14
  • @GilbertLeBlanc 1) We are not past anything, that's what the whole question is about. 2) Yes, most probably I can do with rare expensive updates, especially for the specific project, but I would like to come up with something better anyway. Also, the relevant field is the hotel **location** name 3) You are right. – Giannis Tsim Mar 18 '20 at 19:43
  • A script to modify the DDL for each table is not incredibly hard to do. Once you modify your primary table, you can reference back to it to replace all locations with their new id. You can export, modify, import or do it live. Just make sure you have a good backup in case. Regardless you will "pay" now, or later. I would get it done now instead of doing it every time a location changes. – Nic3500 Mar 18 '20 at 23:55
  • @Nic3500 I edited the question as it was not very clear. I am still designing the database, it is not in production and does not hold any data. My question is about "correctly" choosing the HotelLocation table PK. – Giannis Tsim Mar 19 '20 at 00:32
  • 1
    Ah ok, much easier then! @GilbertLeBlanc 's comment is right on. PK and FK should not be based on mutable values. It will save you much work later. Your model should ensure you do not have to modify it because of new values. Only new characteristics or fonctionnality. – Nic3500 Mar 19 '20 at 00:40
  • Please clarify via edits, not comments. I just said this not clear, so I don't know why you are rhetorically saying this is self-explanatory. "has one-to-many" is a cardinality. It is an aspect of a relationship. What is **the relationship on what entities/values/things** that has that cardinality? The cardinality & other properties like what identifies what *follows from what relationship on what things* it is a property of. This is why I have said, follow a design method & document where you are stuck in it. You literlally don't know what to do. Don't ask us to rewrite such a reference. – philipxy Apr 06 '20 at 07:10
  • Re AKs, I think I misread your table info so I'll try to rephrase that aspect. PS You don't say what a "hotel location" is in business terms, so how do we know how to model it? (Rhetorical.) We don't even know if a "hotel" is a building or a chain. – philipxy Apr 06 '20 at 07:19

1 Answers1

0

I think I understand your question better. You have a Hotel table that looks like this.

Hotel
-----
Hotel ID
Hotel Name
...

Hotel ID is the primary key. It's a blind key, meaning that it has no meaning, other than to identify the Hotel, such as Holiday Inn. It's an integer sequence, starting at zero and incrementing by one for each hotel in the table.

You also have a Hotel Location table.

Hotel Location
--------------
Hotel Location ID
Hotel Location
...

The Hotel Location ID is another blind primary key.

You create an association table to tie these tables together. I'm assuming that a Holiday Inn can have many locations, so there's a one to many relationship betweeen the Hotel table and the Hotel Location table.

Hotel Association
-----------------
Hotel ID
Hotel Location ID
Date location established
...

The primary key is both the Hotel ID and the Hotel Location ID. This is the table that establishes the relationship between the two tables.

You should also have a unique index on Hotel Location ID, Hotel ID, so you can find the hotel for the location.

As you can see, there are some characteristics that have to do with the association, like creation date, update date, and perhaps other characteristics.

Association tables, or junction tables, provide the associations between tables.

Gilbert Le Blanc
  • 50,182
  • 6
  • 67
  • 111
  • Sorry for taking so long. I don't know if you understood my question yet. Anyway, I posted answer but I don't understand, why would i use a junction table for a one-to-many relation? – Giannis Tsim Apr 05 '20 at 20:02
  • @GiannisTsim: You don't have to use an association table. You can put a foreign key in the Hotel Association table if you want. – Gilbert Le Blanc Apr 05 '20 at 22:21
  • I understand you confused what the `HotelLocation` is about. I meant a location inside a hotel (e.x. reception, room,...) and not various locations a hotel as a building may be established. I thought it was clear from the table relations but I also added some sample data for any future reference. – Giannis Tsim Apr 06 '20 at 16:29