Re: database structure for a dynamic product catalogue

[Peter Brawley]

Quote

The paper catalogue consists in 500+ pages.
Every products can span multiple pages.
build a database structure that allow me to update the web product catalogue easily.
...imagine a new product is inserted in the paper catalogue. The page numbers will shift accordingly, based on the insertion point of the new product pages.
I need to build a database structure that allow me to update the web product catalogue easily.

There are two common solutions, (i) a one-dimensional list orderable by product name and/or number (ii) a hierarchy (which may or may not be a tree). In each case, the "page number" of a given product at a given moment depends on what else is in the catalogue at that moment, ie it's dynamic so it's more efficient to compute such a "page number" at runtime rather than try to store it. If there is a real need for persistent page numbers---eg you need to be able to insert pages in a catalogue printout without reprinting the whole thing---then you need an extra table that assigns such page numbers.

In either case there's no need to reinvent the wheel here---browse http://www.databaseanswers.org/data_models/ for catalogues that match your requirement.