1) Using wddx is, well, a really bad idea. How can you describe a product using wddx?
2) Storing XML in a text field is, well, a bad idea. Besides the obvious performance implications, you're already running into problems with having multiple pieces of data glommed into a big field. Seems to me that you've actually lost functionality going the way you're going. I would at least create objects that represent a data layer that you can use for CRUD operations to the database. It would also make sense to create objects that represent things like "product" that use the data layer for attribute filling and functionality. Your database would be set up in a proper normalized fashion. If you really do have 40 fields for products, consider going all the way up to 5th normal form in your DB design. ----- Original Message ----- From: "Alan Quinlan" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, August 20, 2003 3:27 PM Subject: storing xml in sql > Somewhat OT but this is for a CF app and I thought it could be useful for > others too. > > I'm in the processing of making a very flexible OO inventory app. The basic > rundown is this. > > > > You have a table in the DB called "Products". This is where you inventory > goes. Now because this database is going to be used by several different > e-commerce apps, it has to be flexible. App 1 may have a field called > "MODEL" for the item data to go into the table. > > App2 might have a field called "YEAR", and etc. Now if I make new columns > for every app, I'm going to end up with a products table with like 40 > columns. Sure that will work, but its not very organized, and there are > things like, one product cant have more than one model, and etc. > > > > Instead I want to store the data as XML in a text field. Pretty simple > right? > > I format the data using cfwddx. This way I can easily format the data as it > comes in and out of the database. It works great. > > > > But now I ran into a problem. What if I want to search by model? Say I want > all the items in the database that are model "XYZ". Well the only option I > have it to search the xml text field for XYZ, but then I "could" get double > the number of fields I want if any other xml data has "XYZ" in it. > > > > Does anyone know of a good solution to this? Is there a way to search the > specific xml fields while its still in the database? I've looked into the > Microsoft OpenXML, but that seems overly complex. > > > Any ideas would be great. > > > > Thanks, > > -Alan > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
