In a certain business domain, there are three entity types: Address, Merchant and Hotel, and they're related as follows:

  • A Merchant can have many Addresses, and
  • a Hotel can have only one Address at a time.

In my database structure, I want to “use” the same table for both entity types, and I think that I need to have two columns for that, that is to say, merchant_id and hotel_id in the Address table.

I feel this design is not perfect because in many cases either of the two columns would contain NULL marks. What is recommended approach?

  • A hotel is a merchant...of hotel rooms – Neil McGuigan Jul 12 '17 at 20:35

You should not use the same table for entities of the different nature.

In the RDBMS paradygm table represents the type or class. For each specific type/class you have, you should use the separate table. Sure you can mangle the table by bells and whistles like entity_type_id. Sure you can cheat and define your entities in common as "something that have an address(es)".

But the direct and inevitable result of such approach is the complexity and non-obviousness of the queries on that tables. Each time you will be forced to check what exact "subtype" of the entities you deal with. More conditions in the WHEREs, more complex JOINs and their ON's and so forth.

Indeed, there are lot of cases when we are forced to denormalize our databases or to break the ACIDity for sake of performance. But if your primary goal is the clear and comprehensible design - you have to avoid violations of the basic principles. Pretty good explanation you can find in the foundational book "An Introduction to Database Systems" by C.J.Date, part II chapter 5.

As mentioned by Gypsy Spellweaver, you need an auxilliary "pure relationship tables":

+----+-------+-----+    +----+----------+-----+    +----+---------+-----+
| id | Hotel | ... |    | id | Merchant | ... |    | id | Address | ... |
+----+-------+-----+    +----+----------+-----+    +----+---------+-----+

      +----------+------------+    +-------------+------------+
      | Hotel_id | Address_id |    | Merchant_id | Address_id |
      +----------+------------+    +-------------+------------+

Here two bottom tables contains nothing but IDs of corresponding hotels, merchants and addresses.

When you want to know what adresses are associated with some hotel your query is:

SELECT w.*, z.*
  FROM Hotel    AS w
  JOIN H_A      AS q ON q.hotel_id = w.id
  JOIN Address  AS z ON z.id = q.address_id
 WHERE w.Hotel = "Holiday Inn"

Query that returns all the hotels and merchants having the same address:

SELECT w.*, m.*, z.*
  FROM M_A       AS s
  JOIN H_A       AS q ON q.address_id = s.address_id
  JOIN Hotel     AS w ON q.hotel_id = w.id
  JOIN Merchants AS m ON s.merchant_id = m.id
  JOIN Address   AS z ON z.id = q.address_id
  • I am sure that the merchant and hotel are never going to have same address. – CodeYogi Jul 12 '17 at 2:12
  • @CodeYogi That was just an example how straightforward and powerful that approach is. – Kondybas Jul 12 '17 at 14:57
  • I agree but afaik we need third table when there is a many2many mapping in general, wdyt? – CodeYogi Jul 12 '17 at 15:16
  • @CodeYogi Those "pure relationship tables" are already implements M2M mapping. – Kondybas Jul 12 '17 at 15:32
  • But in my case there is not any m2m relationship. Hotel and Address is a o2o and Merchant and Address are o2m. – CodeYogi Jul 12 '17 at 15:42

You need another table, Entity_to_Address with 2 columns, entity_id and address_id. If Address doesn't already have it, it needs a column address_id. From the question, it's presumed that Mercant and Hotel both have an _id type column which is a (primary?) key. Because an address can be assigned to multiple entities, you will not want to place a cascade on the delete from the other tables.

  • 1
    If Hotel and Mercant are different tables their ids can be the same and you can't tell if entity_id is the Hotel.id or Mercant.id. While hotels have the one single address, its id can be stored in the Hotel table. And reference table should contain only relations between Mercant and Address. – Kondybas Jul 11 '17 at 7:44
  • 1
    If the Hotel and Merchant tables have non-mutually exclusive ids then add a entity_type column to the table. The address could, but probably shouldn't, be in the Hotel table. Keeping all addresses in one table simplifies other lookups in the future. It's also conceivable for a hotel to have two addresses: Mailing address, street address, deliveries address. It's also not strictly out-of-bounds for some hotel (A) to be a merchant for another hotel(B). Maybe to supply convention space that exceeds the capacity of hotel(B), as an example. – Gypsy Spellweaver Jul 11 '17 at 7:54
  • Why not have a type column in the address table which can be hotel, merchant etc. and then a resource_id column for the entity so, if type='hotel ' and resource_id=1 then it points to the hotel with id=1. wdyt? – CodeYogi Jul 11 '17 at 17:53

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.