Multiple relasionship problem

[lepixel]

Okay, so there is the pb :

I have to assign "Products" to "Catalogs" (the number of catalogs is variable) and to "Pages" in these "Catalogs"

ex : let's say that ProductId1 must be in Page2 of the Catalog2, in Page2 of Catalog3,..., in PageX of catalogX.

I've tried with 3 tables, one for products, one for catalog and one for pages, i'm not sure this is the good way because nothing works as i was expecting.

[H.P.]

This is how I would solve the problem:

3 tables similar to yours: Product, Page and Catalog

Assumption: 1 product per page

Product = {productID, productName}

Catalog = {catalogID, catalogName}

Page = {pageID, catalogID, productID, pageNumber, pageWhatever}

Assumption: more than 1 product per page

Product = {*productID, productName}

Catalog = {*catalogID, catalogName}

Page = {*pageID, catalogID, pageNumber, pageName, pageWhatever}

Product_Page = {pageID, productID}

An even better approach would be having another separate lookup page table with only pageID and pageNum, but it would make it more complicated to develope (4 & 5 tables).

[Ender]

H.P.'s second example makes the most sense to me. This allows multiple catalogs. Each catalog can have multiple pages. Each page can have multiple products. Products can be listed on multiple pages in multiple catalogs.

[lepixel]

I can understand what you've both written but there is a but,

after the catalogs have been created in the catalog table (ex : "20P Summer", "24P summer", "36P winter"..)

you have to go into the product table and assign the catalogs (ex : product12 has to be in the page 2 of the "20P Summer" , page 2 of "24P summer" and page 3 of "36P winter" ) and this is my problem, since the amount of catalogs is variable, how to select the catalogs AND set a page number ?

I have tried with a value list checkbox formatted which is listing all the catalogs, so I can assign the catalogs BUT how to assign pages for each of the selected catalog ?

to be known :

each product can appear on many catalog but always once in each catalog,

each page can appear in many catalogs but always once in each catalog, page can have many products

[H.P.]

In my last post, I mention that "An even better approach would be having another separate lookup page table with only pageID and pageNum, but it would make it more complicated to develope (4 & 5 tables)"

In this case, you might have to use that approach.

Pls see below for the change in table structure:

Product = {*productID, productName}

Catalog = {*catalogID, catalogName}

Catalog_Page = {pageID, catalogID,*catalogPageID}

Product_CatalogPage = {CatalogPage_pageID, productID}

Page = {*pageID, pageNumber} <- fix for all catalogs

In your product page, you can put a portal that enables the user to select the page and the catalog.

[lepixel]

Yes of course ! i was blind and now i can see )

THANKS FOR ALL,

(great peoples here)