CODEWITHSUNDEEP
sqldatabasedatabase-designrelational-database
noelceta
noelceta

Reputation: 39

Database design: I need an entity in my database to be related to itself but also to be stand-alone

I am sorry if the title of question is not clear. I will try to explain below:

So I am trying to design a database for a Campus Housing App. The Rooms can be either Single Rooms or Apartments. Apartments are basically 2 separate single rooms that have sequential numbers. Meaning rooms numbered 2135 and 2137 are two single rooms that form an apartment. Whereas room 2149 is just a single room.

My Room entity table has the following fields:

Rooms Entity

Am I supposed to add a boolean field IsApartment? But then how can I keep track of which room is connected to the other room that makes up the apartment?

Upvotes: 0

Views: 93

Answers (2)

Stan Winiecki
Stan Winiecki

Reputation: 164

Why not add another table "linkedroom" with fields roomID (JOINed to your room ID) and linkedroomID?

This way you'll have information about the second room if the first one is an apartment, and null if it's not.

Upvotes: 1

Rocky
Rocky

Reputation: 951

You can have 2 different tables - one for single room and another for apartment. Apartment would need 2 columns ( for the 2 single rooms ) and they would act as a composite primary key that would uniquely identify the apartment. You can also define the foreign key from apartment to single_room to ensure that the apartment rooms are definitely single rooms.

Upvotes: 1

Related Questions