May 10, 2005 at 10:07 am
Is Correct to use 2 databases for an application??
I've got a database with general tables like Customers, Employees, etc.. This database is common for other applications that I'll do in a future.
I wanna design other specific database for every application. So, I've got two related databases for my application. The second database has foreign keys which indicate a data from first database.
Is this possible in SQL Server?? Is advisable??
It's possible to create a relation between two tables which belong to different databases??
I'm begginer and I've to take a decision now...
Please, help!
Thank you very much.
May 10, 2005 at 10:12 am
I have applications that use several separate databases, in fact using multiple databases per application is fairly standard in the world of content management systems.
I would be very careful about trying to create referential integrity between different databases. I would have my stored procedures look after that aspect of cross database integrity.
May 10, 2005 at 1:37 pm
Hi,
There was a concept about 5 years ago promoted through the Microsoft seminars to use multiple databases for the application. I am not sure if the concept is still has as much attention but back then they recommended to have an individual database for: application data, user management, state management, user access etc. For example store data in one database. Store views that users have access to in another database, user information in the third database. Customer info in yet another database, application state and or workflow info in yet another database...
There are advantages and disadvantages to this approach from the administration and performance point of view. I guess everything depends on your business rules, performance and security requirments and things like that.
Yelena
Regards,Yelena Varsha
May 11, 2005 at 1:39 am
The approach is also useful in modular applications. For example you may have 3 modules that can run as discreet applications but can also run as a single master application.
May 11, 2005 at 2:36 am
Of course an application can use two or more databases. A database should never be "owned" by an application. That means going back to the pre-relational days of data management, where each application had it's own database(s) and more or less acted as the DBMS for them. In the relational model, a database is used to record facts about some part of the world. A database might be used by 0, 1, 2, 3 .. or whatever number of applications and users.
May 11, 2005 at 3:03 am
Unless theres a good reason to split into two databases, for example some data being read only to majority of users, a database being owned by a seperate accounting system etc. I'd stick with one database especially if you need to keep data in each synchronised, you'll not regret it when writing reports etc.
Phil Nicholas
May 11, 2005 at 5:25 am
Sorry for my poor english, I can't speak english.
I'll explain my problem a little more:
I've a general DataBase, this DB contain tables that will be used by all my applications: Customers, Employees, etc..
I'll make some applications that use the tables that are contained in the general database and also use a new specific tables that are only one for every application. The general tables and specific tables can be relationed. I don't know if is advisable that this specific tables are contained in a specific database.
It's possible that a customer don't wanna install all applications, so I only will install necessary databases. For the reason that you say me (security,performance,etc..) I'm thinking to make a only one database, this databases include necessary tables(general or specific) who should be created by a script when I install the applications..
I'm begginer at programming world and I think that only one database is more easy to handle. Is that right??
Thank you very much for your interest!!
May 11, 2005 at 11:08 am
Hello,
Your English is very good for someone who does not use it as a first language. And your idea is a good idea too. Yes, I think it is good to have one database with general tables and individual databases for individual applications in case you don't want all customers to install all applications. I also know applications that use one database for application data (general data that application is using for it to function) and another database for user data (actual data that users put in it).
Yelena
Regards,Yelena Varsha
May 11, 2005 at 11:21 am
May 12, 2005 at 7:52 am
just keep it simple, make a list of pros and cons and run through list with colleagues.
Phil Nicholas
May 13, 2005 at 3:01 pm
The first issue that occurs to me in your situation, using a single database, is that the database will have different structures depending on the applications the customer wants. That, in itself, could cause confusion. I really think that in your case, multiple databases makes the most sense.
Steve
May 16, 2005 at 1:45 am
The problem to use multiple databases is that if two tables are in separated databases and exists referential integrity between this tables, I must make stored procedures to control it. If the databases have several tables related, I must make very stored procedures that can increase the workflow. And many stored procedures in the application can be very confused also. is that ok??
Thanks!!
May 16, 2005 at 1:45 am
The problem to use multiple databases is that if two tables are in separated databases and exists referential integrity between this tables, I must make stored procedures to control it. If the databases have several tables related, I must make very stored procedures that can increase the workflow. And many stored procedures in the application can be very confused also. is that ok??
Thanks!!
May 16, 2005 at 8:54 am
It is OK.
I work with several industry leaders in CRM (Customer Relationship Management systems) and they do not use referential integrity ability of the database to make their products database engine - independent. Some use stored procedures with good naming conventions. Then there is nothing confusing. For example the procedures that handle relations may be named with the same prefix and have descriptive names. Some other applications handle the referential integrity in the front end - on the application level. For example you have user management in one database. Your workflow is in another database where you need to make sure the user exists in the first database before assigning him a project. You front-end code may manage that. Just make it consistent to have subs or stored procedures to handle Insert, Update and Delete.
Yelena
Regards,Yelena Varsha
May 16, 2005 at 9:02 am
We just add the tables whether they have the additional application or not. This could be confusing for someone writing a report but takes a lot of complexity out of it. Each can have advantages ie having live, test and training dbs on a single server is advantageous to having one set of dbs per server. I havent heard a good reason for seperating the databases other than keeping tables split is neater. Can anyone think of pros and cons obviously anythings possible but is it a good idea and why?
Phil Nicholas
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply