0

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?

5
  • 3rd method - one sparse table. 4th - EAV. 5th - serialization (5a - all, 5b - only extra). ... The task without the goal?
    – Akina
    Commented Feb 6, 2020 at 11:48
  • 1
    The second one is the way inheritance is usually modelled in a relational database. If you don't need strong data type checking, you could also use just a single table with a jsonb column that stores the "dynamic attributes"
    – user1822
    Commented Feb 6, 2020 at 11:50
  • @a_horse_with_no_name Is there any issue with the second way? Using json would seem a bit excessive to me because I do know the types of all of the extra attributes for each type.
    – Levi H
    Commented Feb 6, 2020 at 12:22
  • 1
    It's a matter of taste. Do you want to join the tables all the time to get the additional attributes? Or do you simply want to get them directly from the base table? If it's really only one or two additional columns (and it's like to stay that way) I would probably simply put all of them into one table and simply store null if the columns are applicable. You could use a check constraint to ensure that (together with a "type" column)
    – user1822
    Commented Feb 6, 2020 at 12:27
  • @a_horse_with_no_name Thanks, I went with the second way. I decided to because I don't have that many additional columns and it's likely to stay that way. I avoided the last method you mentioned because I felt it decreased how easy it is to tell which types have which attributes, especially so for foreign keys referencing the table. I thought someone reading the database might not easily be able to tell if another table links to all types in the primary table, or only one or two, instead referencing the "child" table makes it explicit.
    – Levi H
    Commented Feb 7, 2020 at 13:36

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.