From: Steve P. on 14 Jul 2010 14:30 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.
From: Access Developer on 14 Jul 2010 15:52 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(a)monkeybutler.com> wrote in message news:babbf183-1399-486d-82bd-17fbf380a333(a)y4g2000yqy.googlegroups.com... >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. <BIG SNIP>
From: Roger on 15 Jul 2010 14:55 On Jul 14, 12:30 pm, "Steve P." <x...(a)monkeybutler.com> wrote: > 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. so categories have products products have items customers have catalogs catalogs have categories or products or items correct ?
From: Steve P. on 16 Jul 2010 10:36 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...(a)gmail.com> wrote: > But, I would be reluctant to even try to implement something as nearly > totally flexible as you suggest with an unnormalized database -- almost <Giant Snippage> > <BIG SNIP>
From: Steve P. on 16 Jul 2010 11:40 On Jul 15, 2:55 pm, Roger <lesperan...(a)natpro.com> wrote: > > so categories have products > products have items > customers have catalogs > catalogs have > categories or > products or > items > > correct ?- Hide quoted text - > > - Show quoted text - On Jul 15, 2:55 pm, Roger <lesperan...(a)natpro.com> wrote: > so categories have products > products have items > customers have catalogs > catalogs have > categories or > products or > items > > correct ?- Hide quoted text - > > - Show quoted text - 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.
|
Pages: 1 Prev: ADO or DAO? Which one more readable? Next: filter and unbound field |