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.
xyz, there are 2 options:
Use NULL for the Foreign Key
INSERT INTO medicine (name, brand_id) VALUES ('abc', NULL)
INSERT INTO medicine (name, brand_id) VALUES ('xyz', NULL)
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.