Table design for database driven catalog

[Monkey Butler]

I am trying to design a database to store information on products that
will need to be output into varoious print catalogs. Here is my
starting point.

tblItems
++++++++++
ItemId
ItemName
ItemDesc
TableData1
TableData2
etc.

tblItemGroups
+++++++++++
ItemGroupId
ItemGroupDesc
ItemGroupPicture
TableHeading1
TableHeading2
etc.

Note: TableHeading1 is a heading for a table column that will be
populated with rows from tblItems field TableData1.


tblCatalogs
++++++++++
CatalogId
CatalogName
CatalogPrintDate

Here is where I am struggling... I need to define which items a
catalog will contain.

tblCatalogContents
+++++++++++++++
CatalogContentsId
CatlogId
ItemGroupId

But say I have the following item groups and items:

Batteries
AA Battery
C Battery
D Battery

Flashlights
"C" Cell Flashlight
"D" Cell Flashlight

Penlights
"AA" Cell Penlight

In my retail catalog I want to display an Item Group showing all of my
Batteries in a table below the Item Group Picture which is no problem,
but for my Flashlight Item Group I want to show the Picture and both
of my Flashlights in the table but I also wanted to have the "C
Battery" and the "D Battery" listed in the table. I can figure how to
do this but I'd like to be more flexible, so that in my wholsale
catalog the Flashlights are in the table but not the batteries.

I can't figure out a way to relate this.

Thanks in advance for your help.

Steve P.

 

[Fred]

We're doing Access based catalogs here similar to what you describe.

I think that you are saying is that the only difference between the catalogs
is which items are included. If you have only 2 or 3 different types of
catalogs, I'd recommend putting a field for each type of catalog in your
items records which indicates whether or no that particular item is included
in that particular catalog. This presumes an approach that group headers
will print if and only if at least one item for that group is included. Also
that each printing / edition would just get stored as a pdf instead of
getting databased separately. If so, you won't have those catalog tables,
except possible as an independent list.

If you are going to databses each edition of each catalog type as a seperate
catalog, you are going to have lots and lots of data entry work, and forget
everything that I said.

Later on you might find that you'll want other things (besides just
includsion/exclusion of items) change between catalogs, but that's another
story.