4

This is a table for a catalog of medicines. Some have a pharmaceutical brand, others are generic (i.e. they will never have brand information)

CREATE TABLE medicine (
   id serial PRIMARY KEY,
   name text NOT NULL,
   brand_id integer
   CONSTRAINT brand_fk FOREIGN KEY (brand_id) REFERENCES brand (id)       
);

CREATE TABLE brand (
  id serial PRIMARY KEY,
  name text NOT NULL
);

For storing generic medicines, e.g. abc & xyz, there are 2 options:

  1. Use NULL for the Foreign Key brand_id

    INSERT INTO medicine (name, brand_id) VALUES ('abc', NULL)
    INSERT INTO medicine (name, brand_id) VALUES ('xyz', NULL)

  2. Insert just 1 empty string in brand name and use that for all generic medicine's brand_id

    INSERT INTO brand (id, name) VALUES (1, '')
    INSERT INTO medicine (name, brand_id) VALUES ('abc', 1)
    INSERT INTO medicine (name, brand_id) VALUES ('xyz', 1)

From what I've read on StackExchange, it seems like 1 is the general way of doing this. However if I want a unique index on medicine name & brand_id, I'll have to use partial indexes (i.e. 2 indexes respectively where brand_id IS NULL & brand_id is NOT NULL).

If I go with the second approach, I can achieve it with a single index.

Is there any other advantage / disadvantage of either of these approaches. I feel the second method is unconventional and there might be some trouble that I'll discover later along the way.

P.S. I've used the example of medicines to illustrate my query but I want to understand the technical merits of the solution purely from a database standpoint i.e. without going into a discussion of which approach would be better for medicine example.

1
  • I've never seen any questions/usage of second approach. So just wondering why is it not preferred since it can simplify the unique index. Too early to say about performance. – user4150760 Feb 2 '15 at 17:27
7

The answer lies in the ambiguity of your question:

However if I want a unique index on medicine name & brand_id ...

You do not actually want "a unique index". You want to enforce certain rules. A unique index is a means to an end; a tool. The question is: What exactly do you want?

Do you want that there can only be a single instance of a name with an unknown brand? Or can there be multiple instances? That depends on what "unknown" or "missing" or "empty" or NULL is supposed to mean for you.

The canonical meaning of NULL is "unknown". We simply do not know, what goes here, it could be anything, including nothing (empty ('') in string types, 0 in numeric types).

If you want to allow one generic brand, include an entry in brand, call it "generic" or "ACME" or whatever. I like to use the id value 0 for such a catch-all entry.

Then your unique index or constraint is doing what it's supposed to do: Allow only one instance of (name, brand_id) in the table medicine. But multiple entries with (name, NULL) are still possible. Set brand_id NOT NULL, too, if you don't want that.

The best solution also depends on typical queries and details of your setup. Be sure to document exactly what NULL / empty / 0 is supposed to mean and why you chose to design it like that. It may seem crystal clear at the moment you implement it, but may be confusing later.

I added another link to the answer you referenced:

2
  • The requirements are that a brand + medicine name should be unique (i.e. 2 brands are allowed to have same medicine names). For generics, the name by itself should be unique. So exactly what the partial index accomplishes are the requirements. But I don't see any downside of using empty string as Foreign Key either, hence the question. – user4150760 Feb 2 '15 at 17:53
  • @user4150760: For one, a partial index is smaller and only needs to be updated for relevant rows. The first can help read performance, the second can help write performance. Be sure to understand how they work and how you get queries to use them. If in doubt, there are a couple of answers here, try a search. – Erwin Brandstetter Feb 2 '15 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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