Why stick to just one database?
-
I work for a large company which has an odd habit of splitting off logically related tables into separate databases. So instead of one database ABC, you have databases ABC_CLIENT, ABC_PORTFOLIO, ABC_PRICING, etc. To join client data to their own portfolio data, you need to go cross database and to include pricing data means yet another cross database connection. A friend who works for another large company in the same industry says that his company thinks this is "best practice". No one I've talked to thinks this is a good idea, let alone "best practice". What do you think?
I would put them in the same DB, but in different schemas.
-
I would put them in the same DB, but in different schemas.
-
I kind of already faced a similar problem with SOA architectures. If you split your business logic across several self-contained services sooner or latter you'll end up needing to show, on a grid or a report, data that comes from several services. You shouldn't, even if it's possible, do joins across services databases as it breaks all the decoupling principle but when performance starts to be an issue... you know how the story goes from now on don't you?! :) So the only reason I see here (even if it's not a good idea) is an attempt to implement this "SOA" concept but only at DB level (say... Service Oriented Databases? :-\ ), separating "services" by database. Now I'm curious to know if I'm right! :-D
I think you are right but hope you are wrong :) The key to SOA is to implement throughout the vertical, if all is not in SOA becomes an inhibitor and a PAIN.
____________________________________________________________ Be brave little warrior, be VERY brave
-
I work for a large company which has an odd habit of splitting off logically related tables into separate databases. So instead of one database ABC, you have databases ABC_CLIENT, ABC_PORTFOLIO, ABC_PRICING, etc. To join client data to their own portfolio data, you need to go cross database and to include pricing data means yet another cross database connection. A friend who works for another large company in the same industry says that his company thinks this is "best practice". No one I've talked to thinks this is a good idea, let alone "best practice". What do you think?
"Best practice" or "Common pattern", people tend to confuse the 2... Best practice will have justification(s), common pattern probably won't. Best practice is best for specific reasons / conditions, and those will tell you if it applies to you. Try to get the detail of the best practice and you are likely to find out that it is more common pattern. Something like this sounds more like common pattern than best practice, but I still don't see the advantage / point of doing it. IT people (including me) tend to over complicate things, this sounds like one of those.
____________________________________________________________ Be brave little warrior, be VERY brave
-
I work for a large company which has an odd habit of splitting off logically related tables into separate databases. So instead of one database ABC, you have databases ABC_CLIENT, ABC_PORTFOLIO, ABC_PRICING, etc. To join client data to their own portfolio data, you need to go cross database and to include pricing data means yet another cross database connection. A friend who works for another large company in the same industry says that his company thinks this is "best practice". No one I've talked to thinks this is a good idea, let alone "best practice". What do you think?
Are they putting the databases on different physical drives? If so, they might not know about filegroups[^], which would allow them to put different tables from the same database on different drives. Splitting related tables between different databases is a terrible idea. Apart from anything else, you can't have DRI between the tables. You end up using triggers - or worse, external application code - to enforce referential integrity.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
I work for a large company which has an odd habit of splitting off logically related tables into separate databases. So instead of one database ABC, you have databases ABC_CLIENT, ABC_PORTFOLIO, ABC_PRICING, etc. To join client data to their own portfolio data, you need to go cross database and to include pricing data means yet another cross database connection. A friend who works for another large company in the same industry says that his company thinks this is "best practice". No one I've talked to thinks this is a good idea, let alone "best practice". What do you think?
I've seen a system spec written up that required the developer to use SMO to create user-specific tables for each new user in a SQL database, e.g.
[dbo].[JohnSmith_Orders][dbo].[JohnSmith_Profile][dbo].[JohnSmith_Settings]I think you get the idea. The reasoning behind it? "Each user gets their own sub-schema so that no user data leaks into another user" Surely the best way of preventing this would be to have a separate app for each user? There are people out there who should not be trusted with crayons. stuff like this is proof that yes, you really can get your dick caught in a ceiling fan. you just need to try hard enough. -
I've seen a system spec written up that required the developer to use SMO to create user-specific tables for each new user in a SQL database, e.g.
[dbo].[JohnSmith_Orders][dbo].[JohnSmith_Profile][dbo].[JohnSmith_Settings]I think you get the idea. The reasoning behind it? "Each user gets their own sub-schema so that no user data leaks into another user" Surely the best way of preventing this would be to have a separate app for each user? There are people out there who should not be trusted with crayons. stuff like this is proof that yes, you really can get your dick caught in a ceiling fan. you just need to try hard enough.I've seen one worse, an events management system that created a new database for each event. Fortunately, the only involvement I had in the system was recommending it be replaced. I feel sorry for the guys that were trying to report across all the events - especially as the SQL server was having to constantly unload databases to load the new ones...
-
Yep, MS's sample AdventureWorks does exactly this. I don't understand the reasons, but on SQL Server how much penalty is there to having multiple databases?
If the databases are all on the same server instance, there is little performance impact. However if on different server instances, there is a big impact. It complicates issues like disaster recovery - say if one of the databases fails over but the others don't. You also have to keep all the database permissions in synch which can become quite onerous. If a stored procedure accesses tables on 5 different databases it needs to preserve permissions on all of them. You also need to make sure the database-level settings are consistent. Backups need to be coordinated, so if a restore becomes necessary, you're using backups taken at the same time. My issue is when you have related tables and someone splits them into multiple databases ... I can't see any benefit in the splitting and there are ongoing maintenance problems you introduce by doing it. You also have multiple points of failure. You may want to keep tables which are often JOIN'ed on different physical disks for performance, but another member mentioned that you can do this within the same database using FILEGROUPs.
-
I've seen a system spec written up that required the developer to use SMO to create user-specific tables for each new user in a SQL database, e.g.
[dbo].[JohnSmith_Orders][dbo].[JohnSmith_Profile][dbo].[JohnSmith_Settings]I think you get the idea. The reasoning behind it? "Each user gets their own sub-schema so that no user data leaks into another user" Surely the best way of preventing this would be to have a separate app for each user? There are people out there who should not be trusted with crayons. stuff like this is proof that yes, you really can get your dick caught in a ceiling fan. you just need to try hard enough. -
I've seen one worse, an events management system that created a new database for each event. Fortunately, the only involvement I had in the system was recommending it be replaced. I feel sorry for the guys that were trying to report across all the events - especially as the SQL server was having to constantly unload databases to load the new ones...
