Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-08-11 : 08:47:00
|
| Leland writes "We're having a debate at work, the better argument of which will determine the architecture of an application we're rebuilding from the ground up. The debate is about whether it's better to isolate functionality of an application into a separate database (existing on the same server). The component in question is basically a shopping cart application, which our users use to build price lists using product data from our database. The data includes information about manufacturers, product categories, and product details.The lists are then assigned to the users customers so that different customers get different lists.When the lists are created, the data in the ecommerce list tables contain all item attribute except list price. List price is subject to change, so it is pulled from the product table in real time.So what I'm getting at is that once a price list has been created, the only information we get from the main database is the price.One of our stated goals is to create a product that we can sell.For this reason, I've designed our eCommerce application to exist separately from the main database, which, in addition to the product information, includes data related to our business. Functionality wise, the eCommerce application is not part of our Internet site, but is instead part of our customers' store sites. (In my design, authentication is managed by AD, so any customers who need access to our Internet site would be using the same AD account to log into both systems.)To get the product information required to build price lists, I've created four indexed views which collect data from the main database; a select * for three tables, and a view to join these three views into a single result set. The eCommerce application does not update any data in the main database.My counterpart has designed a database that includes the eCommerce functionality within it, and claims that there will be a performance penalty which outweighs the benefits of separating functionality. From what I've read, indexed views out-perform stored procedures on the same data by a significant about, especially in SLQ 2005.He countered that views may be unaware of changes to the underlying data, and I stated that views are fully aware of these changes.In addition to furthering our goal of having a licensable product, I feel that my design is scalable, while a design that tries to be too many things is un-scalable, and more difficult to maintain.To put this into context, the application we're rebuilding used five databases, with data for a given application existing in all five databases. The designers even managed to put stored procs in some of the databases which select 100% of their data from another database.Help me out here, guys. Am I right on this?" |
|
|
airjrdn
Starting Member
35 Posts |
Posted - 2006-08-11 : 11:08:53
|
| I'll preface this by saying it's Friday morning, and I'm fairly tired, so that may be part of why I'm not understanding a portion of your post.It seems you are asking two things; whether or not the indexed views are a good thing, and something about "whether it's better to isolate functionality of an application into a separate database (existing on the same server)"...that's the one I don't get.We did quite a bit of testing on indexed views a while back, but I honestly don't remember what all we concluded. You'd be better off getting feedback from someone with more recent thoughts on that.Regarding the part I'm not understanding, when you say "My counterpart has designed a database that includes the eCommerce functionality within it" how exactly has he designed a database with the eCommerce functionality in it? |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2006-08-11 : 11:12:06
|
| my 2c on 2 minor areasa) "From what I've read, indexed views out-perform stored procedures on the same data by a significant about, especially in SLQ 2005."SP's are the way to go....from a security and code/db isolation viewpoint. SP's enable removing direct access to tables from users...whereas views have to be managed.re performance, there should be neglible (if any) 'measurable' difference. The key is to write the SP's as clean as possible to benefit from caching.b) "He countered that views may be unaware of changes to the underlying data, and I stated that views are fully aware of these changes"Views are fully aware of DATA changes.Views are NOT aware of STRUCURAL table changes...ie new columns. |
|
|
|
|
|
|
|
|
Benefitis of Isolating Functionaly