I have a case where table X can have a one-to-one relation to table Y and at the same time have a one-to-many relation to table Y on the same field. Is it possible to have a one-to-many relation between table X and Y and sometimes use it to insert records that have a one-to-one relation? Will this break the database in any way or cause unwanted future problems? Is it a bad practice?

For example:
company (id, ...other columns)
phone (id, company_id, ...other columns) -> one to many relation

Sometimes a company may have multiple phone numbers and sometimes a company may have one phone number. The case that I have is different, this is just an example similar to what I'm encountering.

What I tried

I tried creating a one-to-many relation between table X and Y (one X to many Y). My code and logic worked and this relation solved the schema problem that I was facing, but I am afraid that using this relation as a one-to-one at the same time is a bad practice that doesn't comply with the A.C.I.D properties.

I also tried creating an external relations table (company_phone):

company (id, ...other columns)
phone (id, ...other columns)
company_phone (id, company_id, phone_id) -> one to one relation to the company table, and one to many relation to the phone table

but the new table relation with the phone table would cause the same problem, it's a one-to-many with the possibility of having a one-to-one in some cases.

I'm truly new to database design and would appreciate any feedback on this.

CodePudding user response：

Let's check the cardinalities for this relation:

company (1, n) own (1, 1) phone

A company can own between 1 and multiple phones.
A phone is owned by only 1 company.

This relation is a OneToMany/ManyToOne as the max cardinalities (the rightmost of each pair) are 1 & n. You must create a foreign key constraint in the phone table as you described.

This does not prevent any company from having only 1 phone as its min cardinality (the leftmost one) is 1. This is a valid scenario.

OneToOne

It would have been a OneToOne relation if the max cardinalities were to 1 on both sides:

company (0, 1) own (1, 1) phone

A company may own only 1 phone.
A phone is owned by only 1 company.

In this case you can create a foreign key constraint in both tables

ManyToMany

Your last scenario using an "external relations table company_phone" would be a ManyToMany relation, happening when the max cardinalities are at n on both sides:

company (1, n) own (1, n) phone

A company owns 1 or more phone.
A phone is owned by 1 or more company.

Here, a company having a single phone is a valid scenario too.