Apartment Housing Type

Question by Jarom Rial

I’m working on creating a database for a website I am developing. In the process of creating the housing table in the database, I came across an issue that I am not sure how to resolve. The table contains apartment complexes for single housing. One of the columns in the table is entitled “tenant_type” and reflects whether a complex is men’s or women’s housing. The unresolved issue is that a number of complexes are both men’s and women’s housing. I thought to change the column name to “type_id” that refers to a look up table named “tenant_type”. I still run into the problem of how to classify a complex as both men’s and women’s housing. Then I thought to make the original “tenant_type” column into two Boolean columns titled “mens_housing” and “womens_housing”. With this solution, whenever there was a men’s housing complex I would put “True” in the “mens_housing” column and “False” in the “womens_housing” column. The women’s housing complexes would be the reverse. Then if a complex was both men’s and women’s, I could put “True” in both columns. I’m not sure about that idea though. Do you have a suggestion for what the best way to approach this might be?

Answer

I believe that either of your two solutions will work:

1. housing_complex table contains type_id column and tenant_type table contains three rows: 1, men; 2, women; 3, both
2. housing_complex table contains two Boolean columns: men and women

There are at least two other solutions you may want to consider:

3. Instead of creating a tenant_type table, you could add an enumerated column to the housing_complex table. The allowed values for the enumerated column are “men”, “women”, and “both”. This solution is roughly equivalent to creating a tenant_type table as you described in your first solution.
4. A fourth possible solution is a housing_complex table and a tenant_type table with a linking table between them. Of the four solutions, this is the most extensible solution but may also be overkill.

Let’s examine each solution in a bit more detail with an ERD, sample data, and a query that lists all men’s housing complexes.

1. housing_complex and tenant_type tables

   

   housing_complex

   cmplx_id	name	office_address	type_id
   1	Bonaventure	1024 Main St	101
   2	Windsor	205 Heritage Dr	103
   3	Beacon Hill	460 Hubalta Rd	102
   4	Glenmore	305 Centre St	102

   tenant_type

   type_id	type
   101	men
   102	women
   103	both

   -- List all single student complexes that serve men.
   SELECT housing_complex.*
   FROM housing_complex AS c
       INNER JOIN tenant_type AS t ON t.type_id = c.type_id
   WHERE t.type = 'men' OR t.type = 'both';

   cmplx_id	name	office_address	type_id
   1	Bonaventure	1024 Main St	101
   2	Windsor	205 Heritage Dr	103

2. housing_complex table with two Boolean columns

   

   housing_complex

   cmplx_id	name	office_address	men	women
   1	Bonaventure	1024 Main St	True	False
   2	Windsor	205 Heritage Dr	True	True
   3	Beacon Hill	460 Hubalta Rd	False	True
   4	Glenmore	305 Centre St	False	True

   -- List all single student complexes that serve men.
   SELECT *
   FROM housing_complex
   WHERE men = True;

   cmplx_id	name	office_address	men	women
   1	Bonaventure	1024 Main St	True	False
   2	Windsor	205 Heritage Dr	True	True

3. housing_complex table with an enumerated column

   

   housing_complex

   cmplx_id	name	office_address	type
   1	Bonaventure	1024 Main St	men
   2	Windsor	205 Heritage Dr	both
   3	Beacon Hill	460 Hubalta Rd	women
   4	Glenmore	305 Centre St	women

   -- List all single student complexes that serve men.
   SELECT *
   FROM housing_complex
   WHERE type = 'men' OR type = 'both';

   cmplx_id	name	office_address	type
   1	Bonaventure	1024 Main St	men
   2	Windsor	205 Heritage Dr	both

4. housing_complex table and tenant_type table with a linking table between

   

   housing_complex

   cmplx_id	name	office_address
   1	Bonaventure	1024 Main St
   2	Windsor	205 Heritage Dr
   3	Beacon Hill	460 Hubalta Rd
   4	Glenmore	305 Centre St

   complex_is_type

   cmplx_id	type_id
   1	101
   2	101
   2	102
   3	102
   4	102

   tenant_type

   type_id	type
   101	men
   102	women

   -- List all single student complexes that serve men.
   SELECT housing_complex.*
   FROM housing_complex AS c
       INNER JOIN complex_has_type AS ct ON c.cmplx_id = ct.cmplx_id
       INNER JOIN tenant_type AS t ON t.type_id = ct.type_id
   WHERE t.name = 'men';

   cmplx_id	name	office_address
   1	Bonaventure	1024 Main St
   2	Windsor	205 Heritage Dr

As you can see from the SQL queries, the first and last solutions should use a join to list all the housing complexes that serve men (or women). The second and third solutions don’t need a join.

The last solution is more extensible than the other three solutions because it would later allow you to define other housing types, such as assisted, summer camp, teen, dog, cat, animal, and allow the same housing complex to serve all the types of tenants. Of course, if you can’t imagine needing this extensibility in the future then choose one of the first three solutions.