Is a "static" FK bad design?

Question

I have a table table1 where records can have different status. If status=10 I need to store more information. In order to keep the db normalised I'm storing the extra information in a separate table, table2.

I want to make sure that table2 records only exists for table1 records where status=10. On way would be to store a constant (10) in table2 so I can have an FK to reference both id and status. This "constant" would be enforced via check constraint.

Is this bad design? Very few records in table1 will have status=10, so the extra storage for table2.status is neglible.

Other solutions?:

Is there any way to define an FK with the (10) included in the definition instead of storing it in table2?
Or, can an FK reference table1 via a filtered index?

CREATE TABLE table1 (
  id int not null
  status smallint,
  PRIMARY KEY (id)
);
CREATE TABLE table2 (
  id int not null
  status smallint NOT NULL DEFAULT 10,
  additional_information text,
  PRIMARY KEY (id)
);
ALTER TABLE table2 
ADD CONSTRAINT table2_table1_fkey
FOREIGN KEY (id, status)
REFERENCES table1 (id, status);
INSERT INTO table1
(id, status)
VALUES 
  (1, 1),
  (2, 1,),
  (3, 10);
INSERT INTO table2
(id, status, additional_information)
VALUES 
  (3, 10, 'additional information..');

score 3 · Accepted Answer · answered Nov 28 '22 at 07:32

I'd say that your design is fine. You could use a generated column for the constant in table2. While this is not necessarily about normalization, there is a lot to say for tables that don't have too many columns in PostgreSQL, because

to access the 50th column, you have to skip the 49 preceding columns
if you update a single column, a copy of the whole row is written

On the other hand, having tables with very few columns is wasteful too, because PostgreSQL has a non-negligible overhead of 23 bytes per row.

Is a "static" FK bad design?

1 Answers1