November 10, 2008 at 10:10 pm
I'm having a big2 performance trouble with Vertical Database Design used for product management.
Here is the background:
Product 1--* Variation 1--* Product_Information 1--* Product_Variation (if the type of product information is another product_variations_ids)
As you can see from that simple relations to retrieve a product information for one product is a nightmare.
Plus, there is a database logic to optimise the storage so for example (if a product variation doesn't have the information which is stored vertically in the product_information it will use the default value from a parent product_variation)
Any suggestion of how to optimize it from Front End or SQL Select?
Personally In performance and storage the DB is small but when you perform a query with complex logic it required a long SQL select and for the front end it required multiple call to database.
Same pain when you perform a DBEntry programming in UI (is a nightmare)
I tried to create simple join and output an XML from SQL. I think it will reduce the SQL Server CPU consumption but, will it kill the WebServer (if it is transformed programmatically), and i'm sure it will kill the user if I spit up the XML or make it a phisical product per product XML file accesible to public.
Would (XSLT + XML) from a physical file better than Database Call?
November 17, 2008 at 4:32 am
It depends.
I'd put variations (like color, shape,...) into detail table. So, on the site you can display products and on the product page let the user choose variations or display product with all variations using outer join:
select ... from products P left outer join product_variations V on P.product_id=V.product_id
This query would output something like:
product1... no variations
product2... variation1
product3... variation1
product3... variation2
product3... variation3
....
November 17, 2008 at 4:44 am
Would (XSLT + XML) from a physical file better than Database Call?
I dont think so. You would miss all the constraints/Fkey relationships that can be used. Your code could become more complex due to all the XML handling required.
I havent gone in detail thru ur mail but i believe a more rationalised design will help you. The scenario that you mention is some what complex but commonly found in different applications.
"Keep Trying"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply