Re: how to link different but related tables

None of this is arguing against Jerry. It's merely thoughts beyond what's written.

On Tue, 15 Mar 2016 11:03:45 -0400, Jerry Stuckle wrote:
> And no, you don't have individual tables for "families of products".

"Families of products" is something that would be best handled with database intersection structures.

PRODUCTS--n-->PRODUCTS_FAMILIES_INTER<--m--PRODUCTS_FAMILIES

This means any product can be in zero-or-more families, and you could, even, have multiple types of families, both externally-known ("the Zeerust Line") and internally-known ("intangibles"). This is, however, a place where documentation is almost more important than design because the design itself is way more flexible than ever needed, and is only limited by how important performance is. Essentially, "_FAMILIES" could end up more like a slew of discontinuous attributes with several subtables describing aspects of attributes and the *design* will cope more elegantly than the code to use it will. DOCUMENT EVERYTHING, because it's possible to go *very* far down that road. (What if you want multiple descriptions or names for an item? Do you need translations of the same text? Etc. Pretty much anything COULD be in this kind of a structure, and you're pretty much on your own deciding)

> You do have independent attributes, however. For instance, color and
> weight are two independent attributes.

And, importantly for thinking about the design, they're intrinsically different kinds of attributes. "Color" might be something that one may want to be multiples, because you want item 123456 (a blue and green striped tablecloth) to come up selecting for either or both of blue AND green. And, color as an attribute is atomic. There's no "fractional" color, you can't calculate off of them, etc. "Weight" however is only ever going to be a single value (which may be zero, but probably don't want null), and you may well need to do math with weights, prices, physical dimensions, etc. Generally speaking, though, if it's not a number that's essentially unbounded and divisible, it's a thing you should ask yourself whether you should be putting in your actual PRODUCTS table.

-- 
Cunningham's Third Law:
The reliability of a complex system is inversely proportional to the
contactability of the person who knows most about it.