I have an Access database that stores product data that I use to
publish a print catalog and run a website. Everything currently works
okay where I only have a single catalog but I would like to be able to
create additional catalogs that use a subset of records in the
database.

Here is an overview of the relevant current tables:



tblCategory
-----------------------
CategoryID, PK
ParentCategory
CatSort, int
CatName
etc.


tblProductsInCategory
-----------------------
CategoryID, FK
ProductID, FK


tblProducts
------------------------
ProdID, autonumber, PK
ProdName, text
ProdDescription, text
ProdImage
AttributeName1
AttributeName2
....
AttributeName10
etc.


tblItems
-------------------------
ItemID, autonumber, PK
ProductID, long, FK
Price, currency
UnitOfMeasure, text
AttributeValue1, text
AttributeValue2, text
....
Attribute Value10, text


The top level categories are actually sections or chapters in the
catalog. Categories might be named Hand Tools, Fasteners or Lighting.
One product may be in one or more categories and consists of many
Items. Products might be things like screwdrivers, screws, flashlights
or batteries. The ProductName, Description and Image associated with
each product represent many items which display in a tabular format.
The Product.AttributeName fields supply column headings for the tables
while the Item.AttributeValues fileds fill the cells below the
appropriate headings. For example a screwdriver might have attributes
of Blade Diameter, Blade Length and Overall Length and an associated
item may have values of 1/4", 8" and 12". I realize that this is
denormalized but I have done this intentionally for ease of data
maintenance.

To build a catalog my code loops through the categorie

To the point of my current problem I want to be able to define
customer specific catalogs that have their own category structure and
use a subset of records from my Product and Item tables. For example
for customer A I might want to include screwdrivers and flashlights
from the product table and assuming that there are 20 related
screwdriver items I want to be able to select only 10 particular
records for this particular customer catalog.

I know I need:

tblCatalog
CatalogID, autonumber, PK
CatalogName, text
CatalogDescription, text
etc.

Beyond that I'm thinking I need to add a table to join the Catalog
table to the Category table or if there is a better way. At this
point I'm struggling to envision an interface that would allow a user
to select a catalog to edit and then add or delete any category,
product or item that is in the database. Any help is appreciated.

You indicated more flexibility for choosing items in your description of the
problem than would be provided just by a Junction Table joining the Catalog
Table and the Category Table. The more options you want to allow (e.g., of
the total number of gadgets, show only 10... which, of course, leads to the
question, "which ten?"), the more complex your implementation will be.

You also indicate "an interface that would allow a user to select a catalog
to edit and then add or delete any category, product or item that is in the
database". How is the user to have access to your database? If you are
asking for design suggestions for a screen on an .asp web page, or an
ASP.NET implementation, there are better places to get that information.

On the other hand, depending on the detail you wish to allow the user to
address, you can, in fact, create a UI that would allow that cabability _in
Access_. As a start, you'd probably be creating a special catalog, just for
that customer, and you'd need to carefully map out how you would query your
database to allow selection to the individual item level -- you very
probably would not want the catalog content information to include ever
individual item to be selected. It appears that you'd want to allow
selection of a particular category, then either selection of or elimination
of subcategories (attributes?), and then selection of or elimination of
individual items.

But, I would be reluctant to even try to implement something as nearly
totally flexible as you suggest with an unnormalized database -- almost
certainly, you have, in fact, complicated data maintenance by not following
relational database design principles, including normalization, rather than
easing it as you intended. Proper database architecture and design are keys
to simplicity of implementation (and sometimes to just possibility of
implementation).

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


"Steve P." <x (AT) monkeybutler (DOT) com> wrote

On Jul 14, 12:30*pm, "Steve P." <x... (AT) monkeybutler (DOT) com> wrote:
so categories have products
products have items
customers have catalogs
catalogs have
categories or
products or
items

correct ?

I think I have a valid reason for denormalizing my Product and Item
tables. A product may have different attributes such as height, width,
length or size, color or hard drive size, memory, processor. An
associated item may have attribute values of 24", 10", 16" or Large,
Red or 500MB, 1GB, P4. I realize that ideally these would be stored in
a separate table where I could add as many attributes as I wanted to
any product or item. But that presents a problem of displaying the
data in a tabular format AND making it editable. A form based on a
crosstab query would display the data as it would appear in a catalog
but as far as I know there is no easy way to make this editable. From
a practical standpoint there is a limit as to how many columns a table
can contain in a printed catalog. I have arbitrarily set this limit at
10. Most items have 4 or less attributes. Anything with more than 10
attributes would usually have these displayed as a bulleted list in
the product description.

If there is a better way to design my database then I will surely
pursue it but I'm hoping that you will agree that my design is
acceptable.

I'll try to further explain the rest of my design problems under
Roger's post.

Thanks,

Steve P.


On Jul 14, 3:52*pm, "Access Developer" <accde... (AT) gmail (DOT) com> wrote:

<Giant Snippage>
> * * * * * <BIG SNIP>

On Jul 15, 2:55*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:

On Jul 15, 2:55 pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:


Yes, I think you understand the model pretty well but let me explain
my design problem a little further.

The basic bulding blocks of a catalog would be each product and it's
associated items. Some examples of products might be Adjustable
Wrenches or Common Nails. Items associated with Adustable Wrenches
might be a 4" wrench, a 6" wrench, an 8" wrench, etc. Items associated
with Common Nails might be 6p (penny), 8p, 10p, etc. So here is the
structure of my basic catalog content:

tblProducts
------------------------
ProdID, autonumber, PK
ProdName, text
ProdDescription, text
ProdImage
AttributeName1
AttributeName2
....
AttributeName10
etc.


tblItems
-------------------------
ItemID, autonumber, PK
ProductID, long, FK
Price, currency
UnitOfMeasure, text
AttributeValue1, text
AttributeValue2, text
....
Attribute Value10, text

I now organize these into categories. This works as a drill down on a
website and also when I output my content to a file for a print
catalog I use VBA code that works very much like the code to populate
a tree view control to loop through the category table and output each
"node" as a product display in the print catalog. Here are my category
tables:

tblCategory
-----------------------
CategoryID, PK
ParentCategory
CatSort, int
CatName
etc.


tblProductsInCategory
-----------------------
CategoryID, FK
ProductID, FK

This currently works perfectly to output a catalog of every item in my
database. Where I am stuck is trying to somehow be able to output only
a subset of Products organized using an entirely different Category
structure.

For example a catalog targeted toward auto mechanics would contain
wrenches, screwdrivers, etc. but not common nails so we eliminate
whole products. A catalog targeted toward jewelers would contain
screwdrivers but only the smallest sizes where a catalog for
steelworkers would only contain the larger sizes so we eliminate
certain items but not the whole product. If I publish a flyer on just
adjustable wrenches it would be a "Catalog" but with only one category
so in my mind each catalog needs it's own Category structure.

Here are screenshots of my data entry form and the printed output if
they are of any help:

http://www.monkeybutler.com/images/untitled.jpg
http://www.monkeybutler.com/images/test.pdf

Thank you for your assistance.

Steve P.