Database design question: discriminatory column vs foreign key

Answer 1

I'd go with having a separate table for distributors with its own key/id. If all distributors are customers then it can foreign key into the customers table.

Eventually you may want to add properties to distributors. Then I'd have a separate table linking markets to distributors (which itself might change over time).

Often entities (e.g., distributors) that start out with only a few properties end up having a lot more.

Answer 2

How many additional properties does a distributor have? just one or two? --> go with a discriminator

If you have plenty of new properties: how many of your customers will be distributors? just a few --> make a separate distributors table with the additional properties.

But if half your customers will be distributors, then it's probably easier to have it all in one table and just have a "customer type" discrimator.

Marc

Answer 3

I would favour your option (1). As you say, if there are no other special fields for a distributor, and you may add further customer types in future, this is definitely the right way to go. (If the situation changes, then that may not be the case)

In reference to:

There is no way to enforce that a market is linked to a customer of the distributor type.

...you could write a constraint that ensures that.

Or possibly even better, make distributors a view of customers and link your market table to that.

Answer 4

Why not get both advantages, by using a discriminator column in customer, and then writing a view:

 create view distributor as 
  select * from customer where is_distributor = 1;

In truth, you should FK customer to a customer_type table, and discriminate on that:

create table customer_type (id int, name, bit is_distributor);

insert into customer_type values (1, 'Customer', 0);
insert into customer_type values (2, 'Distributor', 1);

alter table customer 
 add column customer_type_id int
 references customer_type(id)
;

create view distributor as 
  select a.* from customer a 
  join customer_type b 
  on (a.customer_type_id = b.id and b.is_distributor = 1)
;

Answer 5

In your solution 1, you can force markets to reference only distributors this way:

CREATE TABLE Customers (
  customer_id SERIAL PRIMARY KEY,
  customer_type CHAR(1) CHECK (customer_type IN ('C', 'D')),
  UNIQUE KEY (customer_id, customer_type)
);

CREATE TABLE Markets (
  market_id SERIAL PRIMARY KEY,
  customer_id INT NOT NULL,
  customer_type CHAR(1) CHECK (customer_type = 'D'),
  FOREIGN KEY (customer_id, customer_type) 
    REFERENCES Customers (customer_id, customer_type)
);

A foreign key can reference either a primary key or a unique key in the referenced table.

However, note that if you have distributor-specific attributes that are irrelevant to non-distributors, putting them in the Customers table would violate Third Normal Form. That is, the relevance of these distributor attributes would depend on the value in customer_type which is not part of the primary key. In Third Normal Form, every attribute must depend on nothing but the primary key.

For this reason, I would choose the second solution, making Distributors a child table, referencing Customers. Put distributor-specific attributes into the Distributors table. Then the Markets table can reference Distributors more simply.

Answer 6

Assuming you are going to use some OR mapper, this database details will be hidden from the code. I would decide for a table per sublass mapping because...

1. It resamples the inheritance hierarchy very good.
2. There is no need for an artificial discriminator column.
3. Adding a new subclass requires only adding a new table while using a table per class hierarchy mapping requires the modification of a table in use.
4. There are no useless empty fields for any rows and there will be more and more useless values if you introduce new subclasses into a table per class hierarchy mapping - this is a consequence of the low normalization of the table.
5. You foreign key constraint is easily implementable.
6. Using a OR mapper and round trip engineering the creation of even complex inheritance hierarchies is no pain.

If you think about using Hibernate have a look at Inheritance Mapping in the [N]Hibernate Reference Manual.

Answer 7

I would advise you to go with option 1, the simplest option. It's not (practically) possible to enforce every business rule at the database level.

Consider the consequences of what would happen if a market were linked to a company instead of a distributor and make a decision based on how dire a situation that would create. Here's the "how bad would it be if..." decision matrix I use:

1. Would I get fired?
2. Would I be called at home at night?
3. Would I have to fix it the next morning?
4. Would I have to fix it for the next scheduled release?