SQL table with incompatible columns (only 1 must be used at a time)

Question

Context:

Let's consider that I have a database with a table "house". I also have tables "tiledRoof" and "thatchedRoof".

---

Aim:

All my houses must have only 1 roof at a time. It can be a tiled one or a thatched one, but not both. Even if it doesn't makes a lot of sense, imagine that we might change the roof of our houses many times.

---

My solution:

I can figure out 2 solutions to link houses to roofs:

Solution 1 : Delete/create roofs every time :

The database should looks like this (more or less pseudo sql code):

house{
  tiledRoof_id int DEFAULT NULL FOREIGN KEY REFERENCES tiledRoof(id)
  thatchedRoof_id int DEFAULT NULL FOREIGN KEY REFERENCES thatchedRoof(id)
  // Other columns ...
}

tiledRoof{
  id
  // Other columns ...
}

thatchedRoof{
  id
  // Other columns ...
}

So, I make "tiledRoof_id" and "thatchedRoof_id" nullable. Then if I want to link an house with a tiled roof, I do an upsert in the table "tiledRoof" . If a row have been created, I update "tiledRoof_id" to match the id created. Then, if my house was linked to a thatched roof, I delete a row in "thatchedRoof" and set "thatchedRoof_id" to NULL (I guess I can do it automatically by implementing the onDelete of my foreign key constraint).

Down sides :

• Deleting a row and create later a similar other row might not be really clever. If I change 50 times my roof, I will create 50 rows and also delete 49 of them...
• More queries to run than with the second solution.

Solution 2 : Add "enabler columns" :

The database should looks like this (more or less pseudo sql code):

house{
  tiledRoof_id int DEFAULT(...) FOREIGN KEY REFERENCES tiledRoof(id)
  thatchedRoof_id int DEFAULT(...) FOREIGN KEY REFERENCES thatchedRoof(id)
  tiledRoof_enabled boolean DEFAULT True
  thatchedRoof_enabled boolean DEFAULT False
  // Other columns ...
}

tiledRoof{
  id
  // Other columns ...
}

thatchedRoof{
  id
  // Other columns ...
}

I fill both "tiledRoof_id" and "thatchedRoof_id" with a foreign id that links each of my houses to a tile roof AND to a thatched roof. To make my house not really having both roofs, I just enable one of them. To do so I implement 2 additional columns : "tiledRoof_enabled " and "thatchedRoof_enabled" that will define which roof is enabled. Alternatively, I can use a single column to set the enabled roof if that column takes an integer (1 would means that the tiled one is enabled and 2 would means the thatched one).

Difficulty :

To make that solution works, It would requiere an implementation of the default value of "tiledRoof_id" and "thatchedRoof_id" that might not be possible. It have to insert in the corresponding roof-table a new row and use the resulting row id as default value. If that can not be done, I have start by running queries to create my roofs and then create my house.

---

Question:

What is the best way to reach my purpose? One of the solutions that I proposed? An other one? If it's the second one of my propositions, I would be grateful if you could explain to me if my difficulty can be resolved and how.

---

Note:

I'm working with sqlite3 (just for syntax is differences)

Answer 1

It sounds like you want a slowly changing dimension. Given only two types, I would suggest:

create table house_roofs (
     house_id int references houses(house_id),
     thatched_roof_id int references thatched_roofs(thatched_roof_id),
     tiled_roof_id int references tiled_roofs(tiled_roof_id),
     version_eff_dt datetime not null,
     version_end_dt datetime,
     check (thatched_roof_id is null or tiles_roof_id is null)  -- only one at a time
);

This allows you to have properly declared foreign key relationships.

Answer 2

Are you sure you need to normalize the roof type? Why not simply add a boolean for each of the roof types in your house table. SQLLite doesn't actually have a boolean, so you could use integer 0 or 1.

Note: You would still want to have the tables thatchedRoof and tiledRoof if there are details about each of those types that are generic for all roofs of that type.

If the the tables thatchedRoof and tiledRoof contain details that are specific to each specific house, then this strategy may not work to well.