Re: how to link different but related tables

On 15/03/16 14:18, Stefan Ram wrote:
> mireero <mireero_at_free.fr> writes:
>> Actually, that means that if I have 50 different families of product,
>> I'll have 50 "left join", right ?
>> Ok for 50, but if I have thousands ?
>
> The application domain really might be this complex, or
> maybe the design has missed some possible simplifications.
>
> Maintaining such a database might be difficult.
>
> Performance issues might arise.
>
> But the philosophy of relational databases is not to have
> dynamic attributes, but to hardcode all the attributes of all
> entities at design time. So maybe one than has to bite this
> bullet.
>
> I would not write the SQL queries manually in such a case,
> but generate them from some more abstract formal specification.
> This way, modifications might be easier.
>

The way I handled a similar case was to have a flat table of products, and to build a table of 'categories' to which they belonged, and a third table containing many-to-many links between products and categories.

So a general algorithm might be to find all links that point to a certain (set of) categories and then use that to select details about the products.

Once you get used to link tables it makes all sorts of stuff much easier to think about conceptually.

My product table included all sorts of stuff like weight, dimensions, shipping info and even drawings and manuals that might be associated with it, in the case of a lot of products these things were simply left blank. Empty columns harm no one.

Then you could eg. look for the category of 'products that have manuals' and find all products that should have manuals and see those manuals . Orr id any that didn't.

Orders are in a table that contains the order header.

Order Lines relate products IDs to order numbers, and may need to have separate 'shipped' flags if you part ship an order. For example.

You need to list stuff and use words like 'contains', 'is contained by' 'is exclusively contained by 'is not exclusively contained by' 'has many parents' 'has many children' and so on in order to develop the database structure. This is not something you rush.

-- 
“it should be clear by now to everyone that activist environmentalism 
(or environmental activism) is becoming a general ideology about humans, 
about their freedom, about the relationship between the individual and 
the state, and about the manipulation of people under the guise of a 
'noble' idea. It is not an honest pursuit of 'sustainable development,' 
a matter of elementary environmental protection, or a search for 
rational mechanisms designed to achieve a healthy environment. Yet 
things do occur that make you shake your head and remind yourself that 
you live neither in Joseph Stalin’s Communist era, nor in the Orwellian 
utopia of 1984.”

Vaclav Klaus