Is a "static" FK bad design? [duplicate]

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..');

Answer 1

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

1. to access the 50th column, you have to skip the 49 preceding columns

2. 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.