Search code examples
sqldatabaseumldiagramrelational

Relational model : Company has multiple companies

my problem is the following :

How should I represent in a relational model :

A HQ has at least 0 or more (0,N) companies and those depend of 1 and only 1 HQ.

Knowing that : HQ has many fields similar to companies.

  • A) Should I create 2 tables ? One called HQ and another company.
  • B) Should it be a recursive on the same table ?
  • C) Is there another way to represent this relation ?
Solution

  • Using the same table with a parent field works very well on its own if the HQ has all the same fields as the rest. However, if there attributes of a HQ that are not shared by a company as you say, then you'll also need to have a separate table for the HQ-specific data. So yes, 2 tables. But take jbarker's idea as a starting point. Then add an HQ table with a companyID foreign key. An HQ record will have the companyID of the company that is a HQ, which as he says will have a value of NULL for the parent.

    As for your question about recursivity, you'll have recursive relationships or "self joins" for the company data, and not for HQ-specific data.