Is it good practice to have two SQL tables with bijective row correspondence?

Question

I have a table of tasks,

 id | name
----+-------------
 1  | brush teeth
 2  | do laundry

and a table of states.

 taskid | state
--------+-------------
 1      | completed
 2      | uncompleted

There is a bijective correspondence between the tables, i.e. each row in the task table corresponds to exactly one row in the state table.

Another way of implementing this would be to place a state row in the task table.

 id | name        | state
----+-------------+-------------
 1  | brush teeth | completed
 2  | do laundry  | uncompleted

The main reason why I have selected to use two tables instead of this one, is because updating the state will then cause a change in the task id. I have other tables referencing the task(id) column, and do not want to have to update all those other tables too when altering a task's state.

I have two questions about this.

1. Is it good practice to have two tables in bijective row-row correspondence?
2. Is there a way I can ensure a constraint that there is exactly one row in the state table corresponding to each row in the task table?

The system I am using is postgresql.

Answer 1

You can ensure the 1-1 correspondence by making the id in each table a primary key and a foreign key that references the id in the other table. This is allowed and it guarantees 1-1'ness.

Sometimes, you want such tables, but one table has fewer rows than the other. This occurs when there is a subsetting relationship, and you don't want the additional columns on all rows.

Another purpose is to store separate columns in different places. When I learned about databases, this approach was called vertical partitioning. Nowadays, columnar databases are relatively common; these take the notion to the extreme -- a separate "store" for each column (although the "store" is not exactly a "table").

Why would you do this? Here are some reasons:

• You have infrequently used columns that you do not want to load for every query on the more frequent columns.
• You have frequently updated columns and you do not want to lock the rest of the columns.
• You have too many columns to store in one row.
• You have different security requirements on different columns.

Postgres does offer other mechanisms that you might find relevant. In particular, table inheritance might be useful in your situation.

All that said, you would not normally design a database like this. There are good reasons for doing so, but it is more typical to put all columns related to an entity in the same table.