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

Question

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.

Answer 1

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.