I have a few different items which mostly share the same attributes, but then have a few extra/less. In OOP they could be modeled well with inheritance, but postgres' inheritance is limiting and doesn't seem designed for this. An example of the types might be (this isn't the actual example, so don't take it too serious):
CREATE TABLE document (
id serial not null PRIMARY KEY,
name text,
created_date date,
modified_date date
);
CREATE TABLE document_list (
id serial not null PRIMARY KEY,
name text,
created_date date,
modified_date date,
list_length int
);
CREATE TABLE document_index (
id serial not null PRIMARY KEY,
name text,
created_date date,
modified_date date,
num_index int,
index_header text
);
As you can see they share most values except for a few unique ones. This would fit well with inheritance patterns, but I'm not sure of the best way to implement them in postgres.
I've tried creating document, then inheriting the others from it, but this causes problems with foreign keys, as inherit doesn't support them in postgres.
An alternative way I was thinking about was doing:
CREATE TABLE document (
id serial not null PRIMARY KEY,
name text,
created_date date,
modified_date date
);
CREATE TABLE document_list (
document_id int not null references document(id) PRIMARY KEY,
list_length int
);
CREATE TABLE document_index (
document_id int not null references document(id) PRIMARY KEY,
num_index int,
index_header text
);
Is this a good and/or acceptable way of doing it? Or are there better methods I'm missing?
jsonb
column that stores the "dynamic attributes"