In my database, I have a CUSTOMER table. I'm migrating an OPPORTUNITIES table, which contains two different types of opportunity, each having identical columns (e.g. status, reason lost, probability of success, etc.). One such type is the opportunity of gaining a new customer. The other such type is the possibility of a sale to an existing customer.
I see two possible ways to do this, and am uncertain which is better (or if there's a third, better way).
Option A:
Migrate into two separate tables: POSSIBLE_CUSTOMER and POSSIBLE_SALE. Both tables would have the same columns, including a foreign key to CUSTOMER. POSSIBLE_CUSTOMER's foreign key would have a unique constraint on it.
Option B:
Migrate into a single table, with a column to determine type. This way we're not duplicating the table's columns as above… but there's (to the best of my knowledge) no way to ensure that there's only ever at most one possible customer per customer.
Best Answer
Some people might wince at this, but if you have at least one column that distinguishes sale opportunities from customer prospects you could make this column nullable and create a unique index that includes the customer ID and this column.
Db2 allows only one NULL value in a column in a unique index (in other words, from the uniqueness point of view all NULL values are considered equal). Thus you will store a NULL in this column for "possible customer" and something unique for each "possible sale", and Db2 will ensure that only one "possible customer" (i.e. NULL) value is present for a given customer ID.