represent multiple entities to one entity relationship If suppose, I have following tables:



Here, person can own either home or car. person can't own both at a time. If person owns home, then owns field will be equal to 1, if it owns car, owns field will be equal to 2.

Then, to retrieve what person owns, I will have to fire two queries in application like this:

select first_name, last_name, owns from person where id=1000;

if(owns == 1){
    select id, no_of_rooms, area, city from home where person_id=1000;
}else if(owns == 2){
    select id, make, model, color from car where person_id=1000;
}


Also, what I will do, after first query has executed, someone has deleted things for that person. Then I got in zombie state, I had known person information, but now have no idea about thing he owns.

Or may be I can do like this:



Then query will be:

select p.first_name, p.last_name, t.id, t.type_of_thing, t.no_of_rooms, t.area, t.city, t.make, t.model, t.color from person p, owned_thing t where p.id=1000 and p.id = t.person_id;

if(type_of_thing == 1){
    // process home in application
}else if(type_of_thing == 2){
    // process car in application
}


Which approach is better? Or is there any other better approach than this? I am going to use MySQL database.
Don't use an attribute value to indicate what entity a person owns. Instead, take advantage of the relational nature of the database in your queries. Remove the owns column from person and find related entities by joining the tables:

SELECT 
p.id AS person_id, p.first_name, p.last_name,
h.id AS home_id, h.no_of_rooms, h.area, h.city,
c.id AS car_id, c.make, c.model, c.color
FROM person p
LEFT JOIN home h
ON p.id = h.person_id
LEFT JOIN car c
ON p.id c.person_id
WHERE p.id = 1


A LEFT (OUTER) JOIN will select the desired person whether it owns any entities or not. You can then use logic in your application to detect which owned entities have values and which are NULL. Use an INNER JOIN to only select the person if it also owns one of the entities.