January 5, 2012 at 9:36 am
I have a DB design question...looking to see if its better to redesign the existing database or keep what we have.
Currently, we have one large database that houses everything...Orders, Pricing, Shipping, Web Session, etc...all the norms of a e-commerce website.
My question, is the best way to handle things...or would it be beneficial to have say the Orders/Jobs in one DB, the Pricing in a DB, the Shipping in a DB...having them split into different easier to manage DB's under one instance?
Looking for pros/cons and if this is a good idea or a bad idea.
Thanks.
January 5, 2012 at 9:44 am
I might suggest something but curious to know your thoughts first. Why do you think you should go in this direction? What are the disadvantages / bottlenecks you facing in current setup?
January 5, 2012 at 9:49 am
We are currently in the process of implementing CRM...so the opportunity to split some of the data/tables from our SQL db will be moved into the new CRM db. Because of this 'trimming' of the SQL db...this brings the opportunity to redesign if it were to better server the company.
Example being the Customer, Address, etc (all tables associated with Contacts/Customers) will be moving into the CRM db.
This leaves the other tables...better to keep them in the lets call it STORE db or break it out to Shipping, Pricing???
January 5, 2012 at 9:51 am
I would definitely NOT split it up that way. Split it within the database by schema, if you must split it up, but not accross multiple databases.
First, foreign key constraints can't cross databases, so you can't enforce that kind of integrity in a database split up that way. Do you really want items in your Prices tables that aren't in your Items table, because you split them and no longer enforce integrity on that?
Add that queries that cross databases generally slow down by 3 to 5 percent, because of security tests done and other overhead, and you're looking at a performance hit with no real gain.
The only thing I can think of as a possible benefit to this is that you could split static lookup tables into a database that could be backed up on a different schedule. But you can accomplish that inside one database by splitting into multiple filegroups and backing those up instead of the whole database. So it's not a real benefit, just an apparent one.
What are you aiming to gain from it?
- 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
January 5, 2012 at 9:55 am
This definetly Looks a bad idea, becuase How you are going to Query from two diffrent databases to perform your day to day web operations. joining two tables or updating them from two diffrent databses will be a bad idea. Maintaining diffrent databases also another burden job.
what is the current problem you are facing if they are in single database.
January 5, 2012 at 9:59 am
If it’s a live database and hosting many applications then I would go against your approach. I appreciate your thoughts but I believe a *painful regression testing* is required for all the hosted applications to make sure nothing breaks after database restructure.
January 5, 2012 at 10:02 am
And that's what I was looking for guys...thanks for the input. It was a suggestion that a web developer had...but I was not completely sold on the idea and wanted to hear out a few fellow SQL gurus.
Thanks again!!!
January 5, 2012 at 10:10 am
rodto3rd (1/5/2012)
And that's what I was looking for guys...thanks for the input. It was a suggestion that a web developer had...but I was not completely sold on the idea and wanted to hear out a few fellow SQL gurus.Thanks again!!!
Just adding another thought. If it’s a old system or troublemaker database design (for some reason) it’s the right opportunity to redesign the database (not your approach but some other based on database issues).
But you already know that what all areas you might need to revisit, if you opt so.
January 5, 2012 at 1:04 pm
Just one point worth considering, how much are the differen applications dependent on the data from each other. If it's fairly light, splitting up allows you to have multiple indepdendent development and deployment cycles without major interdependency issues. It's the one big mistake I made when setting up a large system a few years back. I was so focused on interdepdency I didn't focus on the fact that the interdepdency was extremely light. Silly error.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 5, 2012 at 1:31 pm
I've seen stuff like this before. It can be a real pain in the butt to manage and locate the objects you need when their scattered all over a handful of databases. It may seem fairly straight forward at first, put the orders and related objects in one database and the products in another, but what happens when you need to create a stored procedure that needs to do work in both databases? Do you split it into multiple tasks? Do you keep it as one and just pick a database to put it in? I won't even mention the referential integrity problems that QSquared already talked about. I have to deal with this type of stuff right now. It gets worse when developers start doing the same types of things with tables, stored procedures, functions etc. Don't do it unless you have a good reason to do so.
January 5, 2012 at 1:39 pm
On the subject of multiple databases vs. multiple schemas (or other in-database organization methods).
Use different databases when you want to be able to do independent restores of data. If the data is related but non-dependent it can go in different DBs. I've yet to see an order not be dependant on the pricing log so... 🙂
It might make it easier to explain to your webbies that way instead of getting horribly technical on them.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 5, 2012 at 1:42 pm
Thanks guys for all the input...you have given me alot to go back with and inform that we should stick with the existing database instead of trying to split and providing excellent points as to why.
January 5, 2012 at 1:57 pm
I also echo the comments being made on this thread. Unless you have good reason to split out the database, you really shouldn't be....
I had to help support/develop a commercial solution that spread its data over two databases and it was an absolute pain. I really needed foreign keys due to help sort out/stem legacy poor data quality, but couldn't because of the db split. Also made the implementation of service broker much harder as any cross database access required the procedures to be signed with a certificate.. The two databases became very tightly coupled and made disaster recovery that little bit more involved.
Opportunities to refactor the existing schema on the other hand is something that I do support if there are benefits to be had from doing so.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply