Come to think of it that's probably a better idea than the xml approach... And actually... Assuming you don't need text/memo fields, you can just throw all the data into a long varchar or nvarchar field. Then when you retreive content for a given product id, use a group in cfoutput to put all the data into a structure and do everything else the same as I mentioned in the xml approach. It's liable to be faster than the xml approach and also allows you to still draw joins on data in that table.
> Very interesting. How about grouping all the common > information into one table then have another table linked > to it that contains the productID, AttributeTypeID, > Attribute Value. The AttributeTypes can be predefined to > fit all the columns that you don't want in your original > table. So basically, instead of putting the data into > columns, your putting it into Rows. Then build some > standardized functions/sp's to retrived all the data for a > particular product ID, or perhaps create a view that > transforms all the data into a "virtual" table. > James > ----- Original Message ----- > From: Knight, Matt > To: '[EMAIL PROTECTED]' > Sent: Tuesday, July 01, 2003 11:30 AM > Subject: Product Catalog Database Model > I have run into a nasty mess. > I need to consolidate various product catalogs, with > multiple fields of information from each, file > attachments, images, etc. from multiple sub-companies > into a single CFMX application. I have designed the > Components layer, and the presentation layer can be > developed by any of our developers to suit their > specific needs. The data storage is the real mess. I > don't want to create dozens of tables to store all the > relevant data (management nightmare) and I don't want a > few smaller tables with many empty (null) fields (bad > design). XML was a thought, but the number of products > is in the tens of thousands, and looping XML with a file > that large is slow. > Any thoughts, suggestions, lessons learned. > Appreciation in advance > ---------------------------------------------------------- > -------------------- > Matt Knight ►Dresser Corporate Web Team ► Dresser, > Inc. ► w: 972 361 9943 ► m: 214 213 4016 s. isaac dealey 972-490-6624 new epoch http://www.turnkey.to lead architect, tapestry cms http://products.turnkey.to tapestry api is opensource http://www.turnkey.to/tapi certified advanced coldfusion 5 developer http://www.macromedia.com/v1/handlers/index.cfm?ID=21816 ----------------------------------------------- To post, send email to [EMAIL PROTECTED] To unsubscribe: Send UNSUBSCRIBE to [EMAIL PROTECTED] To subscribe / unsubscribe: http://www.dfwcfug.org
