I have a parent table
product, and several children tables like
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.
I later came across this answer to another post and am really feeling positive about it.