3 ms·

You'd have 1 table per sum type which requires extra data. Then polymorphic foreign key (aka a pair of fields school_type, school_id). (No foreign key constrai

• ivank 4y ago
  You can retain foreign key constraints by having one column per type of reference. It is also possible to ensure that exactly one column of several is NOT NULL, so that the columns can always be mapped to an enum in application code. Also, in PostgreSQL, the storage for the extra NULLs uses just one bit per column in a bitmap. CREATE TABLE dirents ( parent bigint NOT NULL, child_dir bigint, child_file bigint, child_symlink bigint, basename text NOT NULL, -- Ensure exactly one type of child is set CHECK (num_nonnulls(child_dir, child_file, child_symlink) = 1), CONSTRAINT dirents_child_dir_fkey FOREIGN KEY (child_dir) REFERENCES dirs (id), CONSTRAINT dirents_child_file_fkey FOREIGN KEY (child_file) REFERENCES files (id), CONSTRAINT dirents_child_symlink_fkey FOREIGN KEY (child_symlink) REFERENCES symlinks (id), PRIMARY KEY (parent, basename) );
• slaymaker1907 4y ago
  Yep, and you can also solve this problem by having a separate table for each variant and joining whenever you need to deal with common stuff. Personally, I think it's usually better just to have one giant table and allow the different columns to be null since it lets you avoid a bunch of verbose joins.