One to one relationship with total participation of one entity. Can a single table be formed?

Question

I am confused whether we need to have 2 tables or a single table when 2 strong entities are in 1:1 relationship with one having complete participation.

Ex: 

How is it possible that we join Person and License into single table as some persons could be there without license as 'Person' participation is not total. Yes, there can not be any license without a person but persons can be without license. If we join these two tables that means some of the persons record may go away. And it could affect the referential integrity as Bank Account may refer to some of the Person records which may go away on joining Person and License.

Comments

If I assume the new entity is also 1:1 then we can add also in the same table with person.Is it total relationship?I am not very sure on this,its very ambiguous to design ER diagram without understanding the underlying functional relationships and the level of normalization expected in database. Check this example link below,here all are 1:1,here they have even not mentioned real valued names of entities or relationships.

https://gateoverflow.in/113407/er-to-relational-conversion

---

More precisely read this discussion,mostly tells you why and why not you should merge.

https://gateoverflow.in/123659/self-doubt

---

"Now what if atleast one side has total participation in 1:1 ?

In this case atleast one column will not be having any Null entries and we can make that column (that is either primary key of E1 or E2) as primary key of whole table and we can merge all into one."

Its in one of the answer provided in the link you shared and this clears my doubt. Yes, we can merge two tables with 1:1 and one side having total participation. Thanks a lot @surajit

Answer 1

If we have 

1) One to one relationship between two Entity

AND

2) Both side total participation

Then we can use 1 table for these both the entity and relationship.

Otherwise we needs two table.

If still there is any doubt then refer this Question : https://gateoverflow.in/168719/gateforum?show=168903#c168903

Comments

I agree with what you said and according to me also 1:1 relationship with both side having total participation could only be merged and its not possible to merge 1:1 relationship with only one side total participation. But at some places, some forums and some answers I have seen that they say its possible to do to i.e. merge when 1:1 relationship and only one side total participation.

---

Even if one has total participation we can have a single table with partial participating entity PK as merged table PK.

https://www.geeksforgeeks.org/mapping-from-er-model-to-relational-model/

---

@smsubham any standard resource to support this answer? Although I can see nothing wrong with the logic, but it may cause many null values to be stored which is why 1:1 is not stored as 1 table unless there is total participation on both sides.. at least that's what I had studied :( What should we follow in gate?

Answer 2

L

l1

l2

l3

l4

Above table is for the Licencse.Consider the below table for the Person.

P

p1

p2

p3

p4

p5

As the relationship is one-to-one and License participates totally then the table for relationship will be

L	P
l1	p1
l2	p2
l3	p3
l4	p4

The primary keys will be L and P.

In case of one-to-one relationship we can merge it with any side  entity.We can notice that if it is merged with L then it will not be altered.Now if we merge it with the person table then the table will look like

L	P
l1	p1
l2	p2
l3	p3
l4	p4
NULL	p5

Now the three tables Person,License and relationship R1 are merged without any violation of the database constraints for 1NF.But for this table L can not be primary key any more because of NULL value.So P will be the new primary key.

Now consider the Account entity and the relationship R2.

A

a1

a2

a3

The relationship R2 is many to one from Account to Person and Account participate totally so

A	P
a1	p1
a2	p2
a3	p2

Now the primary key will be A as there is duplicate value for P attribute.This relationship can be merged with A and primary key will be same.But if we try to merge it with the entity P then the merged relationship will be

A	P
a1	p1
a2	p2
a3	p2
NULL	p4
NULL	p5
NULL	p6

Now for this relationship we can not assign a primary key.P can not be as it has duplicate and A can not be as it has NULL value.

So relationship can not be merged with Person.So we need two separate table one for Person and one for Account and R2.

Now previously we saw that the tables Person,R1 and License can be merged into a single table so we need one table.

So total number of tables needed will be 2.