0

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.

2 Answers 2

0

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.

0

I think you have to see the business on a timeline. Who today is considered a "possible-customer", tomorrow may become a "actual-customer". More importantly you have to think also that it may be "possible-sales" associated with "possible-customers" as well as "actual-customers". So, it may be better to have just one table for (both actual and potential) customers and manage the status of the life cycle of the customer in a column (with two or more status, such as potential, active, in-default, inactive, etc). With respect to the OPPORTUNITIES table, things may be different and there are good reasons to keep separate OPPORTUNITIES from SALES.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.