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
| improve this answer | |
  • 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

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.