Postgresql – Correct way to join row in parent table to row in one of several children tables

hierarchyinheritancejoin;postgresql

I have a parent table product, and several children tables like phone, tablet, smartwatch etc. The parent and children have 1-1 relationship. This means that for every row in parent table, there will be exactly 1 row in exactly one of the several children tables. This is to basically pull up common fields in a single table (like product.product_name), in my effort to follow the DRY principle.

Now the part where i am worried is that, although i can easily join to the parent row, provided the child row, how would i efficiently do vice versa.
For example: if i have a row from the product table, and now i want to get the rest of the product information, how would i quickly find which child table contains the rest of the information, other than having to check each and every child table in a big query. In simpler words: How to efficiently find the correct child table in order to join a row in parent table to its corresponding row in one of the children tables.

Let's assume i would soon have 100+ children tables. In such a case, an exhaustive query would get bigger and bigger with every new child table.

One obvious solution would be to maintain a 'product_type' field in the parent table. But this is clearly redundant information, violates the Single Source Of Truth rule , and prone to human error (eg; product_type says 'phone' but the product actually was a 'tablet'). So i am not sure if this is the recommended way to do it.

I am meaning to find out the canonical way to solving such a problem when designing schema, and not just an opinion-based answer.

I am sorry if this wasn't the right forum to ask this question.

UPDATE:

I later came across this answer to another post and am really feeling positive about it.

Best Answer

I think your whole schema is not fully thought through.

A product is a product which, presumably, you are going to sell?

Have one product table - no need to split the products up into phone, tablet or smartwatch. There's no problem having millions of records in a table these days.

Have a table product_category (or type as you suggest) which will have phone, tablet &c. which will be a FOREIGN KEY (pointing back to an INTEGER PRIMARY KEY) in the product table. Then you can have your classic supplier, client, billing tables &c.

You might want to take a look at databaseanswers.org and also you could download Open Source products which correspond to your particular system - check out their schemas and see what they've done.

You asked about the canonical way of designing these sorts of schemas - this is (probably) it. Plus, you won't be writing queries with 100's of tables. You mentioned DRY and SPOT. Don't forget YAGNI (You Ain't Going To Need It) - also espoused by Eric Raymond in the excellent "The Art of Unix Programming".

Don't try and be all things to all men from day one! Start easy and design incrementally - don't expect to get everything right the first time (hint: you won't :-)) - best of luck with your project.

Related Solutions

Oracle hierarchical query question (start with … connect by … )

There would have to be data indicating that the row is both a child of row A and a child of row B. This can be done, but does not seem like it would be typical. Think of an employee. Most employees only have one direct managers. In a simple design an employee could have two managers, but it would require multiple employee records. For example:

--drop table e;
create table e (id number, name varchar2(100), manager number);
insert into e values (1,'Amos',null);
insert into e values (2,'Bob',1);
insert into e values (3,'Cindy',1);
insert into e values (4,'Dave',2);
insert into e values (4,'Dave',3);
select substr(name,1,7) name, substr(prior name,1,7) Manager
   from e start with id=1 connect by prior id=manager order by name;

Postgresql – Query parent table and get child tables columns

As long as the set of inherited column values is unique across all tables, there is a simple solution with a NATURAL join (one of the rare use cases for this clause!):

SELECT * FROM ONLY parent NATURAL FULL JOIN child;

Since NATURAL is (per documentation):

... shorthand for a USING list that mentions all columns in the two tables that have the same names.

You get each column only once without explicit SELECT list, and rows from the parent table are extended with NULL values for added columns - exactly the way you desire.

This even works with NULL values in any of the columns.

To also see the origin of each row in the result, insert another row to demonstrate the difference:

INSERT INTO child(col1) VALUES(1);

Then:

SELECT COALESCE(p.tableoid, c.tableoid)::regclass AS tbl, *
FROM   ONLY parent p NATURAL FULL JOIN child c;

 tbl   | col1 | col2
-------+------+----
parent | 1    | NULL
child  | 1    | NULL
child  | 2    | 2

SQL Fiddle with more test rows.

You can join to multiple child tables this way if all additional column names in all child tables are unique. Else you'll have to spell out an explicit SELECT list and explicit join conditions.

The set of inherited column values has to be unique across all child tables.

Best Answer

Related Solutions

Oracle hierarchical query question (start with … connect by … )

Postgresql – Query parent table and get child tables columns

Related Question