Self referencing relationship

Question

I'm currently designing a contact management database as a rookie. I've encountered the following scenario:

I have created a table company for company information. Within the information I want to include the parent company, if they have one (for example: Ferrari is one of the company, but its parent company is Fiat, which is also one record in the company table).

I am not sure how I should approach this problem. Should I add a parent_company column to the company table and self-reference OR should I make a new table to store the parent-child relationship?

Answer 1

I would recommend that you add parent_company (nullable) column in company table and reference it with company id. As I believe you are not gonna have multiple parent company for a sub-company, so no use of creating a new table. For many to many relationship you'd need a new table.

You can create foreign key reference like this

ALTER TABLE company ADD COLUMN parent_company INT UNSIGNED NULL, ADD FOREIGN KEY fk_parent_company REFERENCES company(id)

Use UNSIGNED if your table id has UNSIGNED attribute. Basically you have to follow exact column attributes for the new column, except make it nullable.

Hope this helps.

Answer 2

Managing hierarchical data is tricky, particularly in MySQL which really has no built-in support.

If you knew in advance that almost all companies were singletons and here and there you had a parent company, then adding a parent_company_id to your table.

However, the situation can get more complex. That is why I would suggest that you consider some alternatives on how to store the data. For instance, it can be handy to store the entire hierarchy to the parent company. Here is a good article on storing hierarchical data.