2

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.

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.

  • Thank you for your answer :) I must mention that keeping just one table would mean leaving a lot of fields empty (smartwatch_band_color field would mean nothing to a phone type row). But 'Don't try and be all things to all men from day one' is very true! – Abdul Wasae May 9 '18 at 5:25
  • You could have a description text field - you didn't tag the question with the RDBMS you are using! You could use JSON if it's PostgreSQL? – Vérace May 9 '18 at 11:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.