Use conditional constraint or normalize table?

Question

I have two entities: A and B. The latter has two types B1 and B2. Their relationship is:

1. (A,B1) is (1:1)

2. (A,B2) is (1,N)

When creating database tables, I wonder how is the best way to guarantee consistency. I can see two options: (1) Create one table and use a conditional constraint that swaps the consistency strategy based on B type or (2) create two tables, one per B type, and set each consistency strategy separately.

---

A note on the table usage:

• The RMDBS is PostgreSQL;
• The read operations are much bigger than the write operations;
• The write operations of B2 are much bigger than the write operations of B1.

Answer 1

Generally, it is better to normalise, unless there is a strong reason not to (like performance issues).

If both B1 and B2 must share a unique ID sequence (b_id), that can only be done within a single table: B. If they can have separate ID sequences (b1_id, b2_id), it will probably be easier to create separate tables: B1, B2.