How do I create a relationship database?

Question

Hello All, I work for a ministry that helps people getting out of prison. I've already written them a database tracking application so they know where people are and who is coming in. So, I'm kindof familiar with a one table database. But, they've presented me with a new problem. They have a mobile home park with Mobile homes, travel trailers, and now they are building apartments. In the one table database I have where the person lives. E.G Doe, John lives in 34B But, that's not good enough. They want to know if in a mobile home that can house 3 people, who lives in what room. Now, they've given me an example. Idle, Billy is in Room X, Joel, Billy is in Room Y and Ocean, Billy is in Room Z. All in Mobile Home #100. I hope that makes since.

So, I've spent many hours researching this and I'm at my wits end. I have tried many different things with relational tables but it's not making since.
I thought if I try One table lets call it Tblresidents with columns ID, FullName, HousingID and this was the PK. And then another table called
Tblhousing with the column HousingID FK. But, when I look at it it doesn't make since. I have found that I have to input the mobile home lot numbers in for it to work with the names but what do I do with the Room numbers?

I really hope someone could make since of this. Thanks guys.

Answer 1

Hi @Dano , welcome to Microsoft Q&A forum.

To handle this situation, we need to come up with multiple normalized tables. Few of them could of Master table and others will be transaction table. Please refer to below tables with description:

TBLHomeType - This is master table which will have information about different home types like mobile home, travel trailers etc.

TBLHomes - This is master table which will have relation with home type with mobile home numbers.

TBLHomeRoom - This table contains the data about which home has how many rooms and what is the name of those rooms.

TBLResidentsHome - This table contains the data about which resident lives in which home and which room.

Now if we need to find which resident lives where we can use below query directly:

SELECT TRH.ResidentId,TRH.FullName,THR.HomeId,THR.RoomId FROM TBLHomeRoom THR  
INNER JOIN TBLResidentsHome TRH ON THR.HomeRoomId = TRH.HomeRoomId  

If we would like to know empty rooms, we can fire below query:

SELECT THR.HomeId,THR.RoomId FROM TBLHomeRoom THR  
LEFT OUTER JOIN TBLResidentsHome TRH ON THR.HomeRoomId = TRH.HomeRoomId  
WHERE TRH.ResidentId is NULL  

Again this is provided based on the details you mentioned. If you can go through these and check if this meets your requirements or not. Or else you can mention more details and we can figure out what is the best way to cover this.

----------

If answer helps, please 'Accept Answer' as this could help other looking for similar queries.