I'm new to relational databases having enjoyed the flexibility of nosql to date.

I want to store data about 'estimates'. An estimate takes a set of parameters that depend on which product it is an estimate for. I think there are two ways forwards:

Tables for each product category

I was planning to have a table for estimates and one for each product category (a category could be "cards", and this might cover product such as "business cards" and "postcards":

estimates (id, name, user_id, category("Cards", "Books",...), linked_id (=one of the ids in the following tables)
cards(id, product, size, weight)
books(id, product, fold, coating)

I've read on Stack overflow some posts about conditional joins, but never got the impression that this was 'best practise', although I also did not see alternatives.

A table where not all fields are completed

Here I would have an estimate table that has every possible field for products, but only use those that are relevant for the product stored in that row,

estimates (id, name, user_id, category,size, weight, fold, coating, ...)
# example row
# id: 100, name: Recipe book, category: book, size: 77, weight: 20, fold: null, coating: null,...

Does the above make sense; what do I need to watch out for?

  • 1
    Estimates are not products - IMHO, use a separate table with product type and id as a foreign key. You can't simply use id (BTW, use book_id, card_id etc.) as you're not guaranteed to have unique ones for each table. – Vérace Apr 26 '16 at 11:40
  • I need you to clarify what you want to do to give you precise indications; are the size, weight,fold and coating the "parameters" of the "estimate"? Elaborate more on what you're doing, as it stands I don't thing that anyone without your knowledge could understand what you wrote. – gab Apr 27 '16 at 10:49
  • One thing I can tell you right now is that most likely your major mistake is to try to refer to tables dinamically (putting their names in fields and using them in an expression - conditional joins - or whatnot); in general in relational databases you can't do it, or it leads to suboptimal flexibility and performance. To advise you on better solutions I need more information. – gab Apr 27 '16 at 10:49
  • And in general you should probably read as soon as you can at least some introduction to relational databases, you're poised to make a lot of unsavory mistakes otherwise. – gab Apr 27 '16 at 10:50

Your Answer

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

Browse other questions tagged or ask your own question.