April 2, 2010 at 10:38 am
Hi,
I need some advice on what's the best path for my scenario. I will try my best to describe the most important part of my scenario.
Scenario:
1. One ASP.NET application that serves multiple e-Commerce stores (The front end changes the back-end does not).
2. There are potentially 100 stores.
3. I have to keep each store's data separate because of business requirement (are there any advantage for storing everything in one database and one schema that could possibly help me change the business requirement) The business requirement is mainly to keep everyone's transaction and prices separate and to easily provide this data if the decide no longer to be part of this web-app.
4. I want to keep the overhead and maintenance as minimal as possible.
5. There will be a master database/schema that stores all the common product details (600K products).
6. I will most probbaly be able to obtain SQL Server 2008 Enterprise Edition and thus will have access to partitioning.
Questions:
1. Is it better to use multiple schemas or databases to meet the business requirement?
2. If I do use multiple schemas or database, what can I do to not replicate the same stored procedure in each schema.
Any advice would be great.
Thanks,
Pranav
April 2, 2010 at 10:41 am
I'd do multiple databases. Makes it easier to scale the whole thing out, plus lots of other advantages.
On the need for a single place to keep procs, would it be easier to have a core database where you roll out new code, and then use replication or some such to move the procs to each of the other databases? RedGate's multi-script product might be a good way to do that too.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2010 at 10:56 am
Hi,
Thanks for the response, I have never used multiple databases in this scenario, not 100. I am coming from Oracle experience for the last few years where you create schemas for everything and separate databases are only created when necessary.
Would there be any performance issues that I would have to worry about between master and retailer databases? Since all the lookup data regarding products is in the Master database?
I am still wondering as to exactly what are the advantages of choosing Databases over Schemas.
Thanks
April 2, 2010 at 2:35 pm
Pranav Shah-410191 (4/2/2010)
Hi,Thanks for the response, I have never used multiple databases in this scenario, not 100. I am coming from Oracle experience for the last few years where you create schemas for everything and separate databases are only created when necessary.
Would there be any performance issues that I would have to worry about between master and retailer databases? Since all the lookup data regarding products is in the Master database?
I am still wondering as to exactly what are the advantages of choosing Databases over Schemas.
Thanks
This is not really data modeling question because most of your users are anonymous users in your session object. But you need 100 shopping carts that includes customer table because most Ecommerce users browse and may never buy anything. So it is much easier to run 100 shopping carts databases than to run one database with 100 schemas each running a shopping cart because your connection resources are limited and the database could grow too big very quickly. There are existing Ecommerce components for the Microsoft platform you could test drive.
That takes me to why are you writing one? When you could add an existing Ecommerce component to your application for a few dollars.
Kind regards,
Gift Peddie
April 2, 2010 at 2:56 pm
Hi,
I am looking at those options too, could you recommend any particular components.
Thanks
April 2, 2010 at 3:20 pm
Pranav Shah-410191 (4/2/2010)
Hi,I am looking at those options too, could you recommend any particular components.
Thanks
Check the three links below the first two are free but the last one is not free, you also need to check Codeplex for more Ecommerce free code you could modify for your needs. If the shopping cart is in XML it may not scale.
http://www.microsoft.com/web/gallery/nopCommerce.aspx
http://www.aspdotnetstorefront.com/
Kind regards,
Gift Peddie
April 2, 2010 at 3:34 pm
Pranav Shah-410191 (4/2/2010)
Hi,Thanks for the response, I have never used multiple databases in this scenario, not 100. I am coming from Oracle experience for the last few years where you create schemas for everything and separate databases are only created when necessary.
Would there be any performance issues that I would have to worry about between master and retailer databases? Since all the lookup data regarding products is in the Master database?
I am still wondering as to exactly what are the advantages of choosing Databases over Schemas.
Thanks
With the requirement to keep the data for each store separate, you should use multiple databases - 1 per stored.
If that requirement can be modified, then you can use a single database with a store table that is referenced by orders and customers.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2010 at 7:33 pm
Pranav Shah-410191 (4/2/2010)
I am still wondering as to exactly what are the advantages of choosing Databases over Schemas.
Backup/recovery strategy would be much easier and reliable with one databse per store.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 5, 2010 at 6:23 am
The main advantage to multiple databases is the ability to scale out.
If you find that there's enough traffic to overload the server, you can move the busiest databases to another server, or move half of them, etc. Very easy to do with multiple databases. Major pain to do if it's all in one database and just split into multiple schemas.
Other advantages include backup and rollback scenarios, where isolation by database is easier to manage than isolation by schema.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 5, 2010 at 8:31 am
Thanks guys, this helps me a lot. At least now I know why multiple databases are better than multiple schemas.
Additionally I was planning on creating Materialized views in each database so that they only contain the product data for the particular store.
The master database would have about 500K products, but each store might only have about 50K products.
Just so that I can avoid going to the master database when a search pattern is entered, I am thinking about creating materialized views in each database so that it only contains the necessary data for the store.
Any advice on this plan.
Thanks.
April 5, 2010 at 10:40 am
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2010 at 11:10 am
Pranav Shah-410191 (4/5/2010)
Thanks guys, this helps me a lot. At least now I know why multiple databases are better than multiple schemas.
Glad to help
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 6, 2010 at 4:39 pm
Let me make an assumption that when you say “separate”, the data can still reside in the same database as long as the users only see their data. If that’s the case here’s another angle to consider - I’ve built a similar application/database structure and the way I’ve tackled the separation of data is via a “data owner” foreign key. Each table in the database carried the “data owner” foreign key and I would pass this value to the stored procedure which would then filter the data based on the user’s “data owner” key. The advantage of this method is there is only one database to maintain, one set of stored procedures and triggers to work for all stores, your SSRS reports should also be able to work with the same database without having to shift from one database to the other or one schema to the other and most of all because it’s data driven you can dynamically add new stores without having to create a new database and/or a new schema each time a new client comes on board.
I’ve implemented this design methodology to both Oracle and SQL Server for two different projects and it worked perfectly fine. The only caveat is that you have to be very judicious in designing your stored procedures and make sure that you always pass down the “data owner” key when retrieving, updating or deleting their data. One more advantage to this design is that you can have a “master” view of all the data if you need it.
April 6, 2010 at 5:50 pm
I’ve implemented this design methodology to both Oracle and SQL Server for two different projects and it worked perfectly fine. The only caveat is that you have to be very judicious in designing your stored procedures and make sure that you always pass down the “data owner” key when retrieving, updating or deleting their data. One more advantage to this design is that you can have a “master” view of all the data if you need it.
The problem with your design is on the base level the OP needs 700 tables that is 100 very small shopping carts with a customer table. If you think 100 shopping cart is not needed then you don't know http and more important keeping track of your anonymous users in session. That is the reason I have not answered the OP design posting because if you test drive existing cart you will see that it is not a simple owner issue.
The most important issue for the OP is tracking users activities in session one of the most complicated areas in Asp.net. And if you are one of those developers who put the shopping cart in an XML CDATA section then I would not be talking to you because that is invalid implementation of a shopping cart.
Kind regards,
Gift Peddie
April 7, 2010 at 8:59 am
Pranav Shah-410191 (4/5/2010)
Thanks guys, this helps me a lot. At least now I know why multiple databases are better than multiple schemas.Additionally I was planning on creating Materialized views in each database so that they only contain the product data for the particular store.
The master database would have about 500K products, but each store might only have about 50K products.
Just so that I can avoid going to the master database when a search pattern is entered, I am thinking about creating materialized views in each database so that it only contains the necessary data for the store.
Any advice on this plan.
Thanks.
If you have a central database containing all the product data, you won't be able to create on indexed view in the individual store databases to materialize the individual store data.
From BOL:
A view must meet the following requirements before you can create a clustered index on it:
The ANSI_NULLS and QUOTED_IDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed. The OBJECTPROPERTY function reports this for views through the ExecIsAnsiNullsOn or ExecIsQuotedIdentOn properties.
The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.
The view must not reference any other views, only base tables.
All base tables referenced by the view must be in the same database as the view and have the same owner as the view.
The view must be created with the SCHEMABINDING option. Schema binding binds the view to the schema of the underlying base tables.
User-defined functions referenced in the view must have been created with the SCHEMABINDING option.
Tables and user-defined functions must be referenced by two-part names in the view. One-part, three-part, and four-part names are not allowed.
All functions referenced by expressions in the view must be deterministic. The IsDeterministic property of the OBJECTPROPERTY function reports whether a user-defined function is deterministic. For more information, see Deterministic and Nondeterministic Functions.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply