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?