Re: database structure for a dynamic product catalogue
Posted by: Rick James
Date: July 10, 2014 11:45PM

Page numbers only matter if you reprint the catalog after inserting new page(s). Is that what you intend to do?

SET @product = 'FooBar'; -- name of new product
SET @length = 5; -- to say the new product takes 5 pages
UPDATE Products SET start_page = start_page + @length WHERE product_name > @product;

It might be better to write that as a Stored Procedure with two arguments.

Note: The UPDATE will have to churn through much of the table, but you have only a few hundred rows in the table, so it is not a big deal.

The other table (Pages) would have
product_name -- for JOINing to Products
page_number -- 1,2,... - pages within the product, not within the Catalog
page -- image (or whatever) of the page

Actual page numbers would be
Products.start_page + Pages.page_number - 1

Let's see these:
SHOW CREATE TABLE -- for each table
SELECTs & INSERTs -- for the typical activities.

Options: ReplyQuote


Subject
Written By
Posted
Re: database structure for a dynamic product catalogue
July 10, 2014 11:45PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.