CODEWITHSUNDEEP
mysqldatabase
Feal
Feal

Reputation: 3

Relational database and PHP: one-to-many relations with multiple one-tables

Let’s assume there are some rows in a table cars, and each of these rows has an owner. If this owner were always a person (conveniently situated in a table persons), this would be your standard one-to-many relation.

However, what if the owner could not only be a person, but also a company (in a table companies)? How would this relationship be modeled and how would it be handled in PHP?

My first idea was to create a column person and a column company and check that one of them always stays NULL, while the other is filled – however, that seems somewhat inelegant and becomes impractical once there is a higher number of possible related tables.

My current assumption would be to not simply create the foreign key as an integer column person in the table, but to create a further table called tables, which gives IDs to the tables, and then split the foreign key into two integer columns: owner_table, containing the ID of the table (e.g. 0 for persons and 1 for companies), and owner_id, containing the owner ID.

Is this a viable and practical solution or is there some standard design pattern regarding such issues? Is there a name for this type of problem? And are there any PHP frameworks supporting such relations?

EDIT: Found a solution: Such structures are called polymorphic relations, and Laravel supports them.

Upvotes: 0

Views: 444

Answers (1)

Arkh
Arkh

Reputation: 8459

There are multiple ways to do it.

You can go with two nullable foreign keys: one referencing company and the other user. Then you can have a check constraint which assure you one is null. With PostgreSQL:

CREATE TABLE car{
    <your car fields>
    company_id INT REFERENCES car,
    person_id INT REFERENCES person,
    CHECK(company_id IS NULL AND person_id IS NOT NULL 
       OR company_id IS NOT NULL AND person_id IS NULL)
};

Or you can use table inheritance (beware their limitations)

CREATE TABLE car_owner{
    car_owner_id SERIAL
};

CREATE TABLE company{
   <company fields>
} INHERITS(car_owner);

CREATE TABLE person{
   <person fields>
} INHERITS(car_owner);

CREATE TABLE car{
   <car fields>
   car_owner_id INT REFERENCES car_owner
};

Upvotes: 1

Related Questions