August 20, 2003 at 1:49 pm
I'm not entirely sure of where to post this question, so here goes. Can anyone share with me their insights on setting up databases on SQL 2000 servers. I have receiently changed jobs and where I currently work has their database set up differently that I have ever seen.
Example: where I did work had a server, let's say server1, that contained multiple databases that cooresponded with applications. There was also a database on server1 that contained login information that each applications security rights were set against. So, basically I went to multiple databases on the same server for data to run the app. I did not query against these databases using stored procedures. The select statements were hard coded in the app.
At my new place of employment they have only one server, lets call it server2, which has a major whopper of a database on it that containes well over 400 tables. It is my understanding that I am to place any new tables I need to create in this database. Based on my past experience I find this to be cumbersome, that you would have the possibility of a lot of garbage residing in that database because it seems unmanagible to maintain. I asked why they did it like this & was told it was because of using stored procedures.
OK, so my question becomes how should a database be set up if you need multiple lookup tables to be accessed by many apps.
Can stored procedures be written querying data from 2 databases on the same server?
What does everyone do to maintain the database where they work?
Thanks for any insights.
August 20, 2003 at 2:09 pm
quote:
Can stored procedures be written querying data from 2 databases on the same server?
Yes.
quote:
What does everyone do to maintain the database where they work?
We generally use a single large database for each application, we use very, very little SQL outside of stored procedures, and we have a "controlling" application database that houses tables that may be common to multiple databases.
quote:
I asked why they did it like this & was told it was because of using stored procedures.
This is not a reason to have a single database instead of multiple ones, AFAIK; in fact, it sounds like a plain old excuse. 400 tables in a database is not particularly large, nor unweildy if managed and documented correctly. If the company you work for currently has 400 tables with no coherent naming convention, and no E-R diagramming to speak of, then you've got big problems.
August 20, 2003 at 2:18 pm
jpipes,
Thanks, for responding so quickly. Could you please give me some insight/examples of how to write a stored procedure that joins data from a table in one database with data in another database?
Also, can you expand more on the "Controlling" application database?
Thanks,
Corinne
August 20, 2003 at 2:19 pm
quote:
Can stored procedures be written querying data from 2 databases on the same server?
It is nothing to do with the stored procedure. Suely you can query data from different databases by using full qualify name like databasename.objectownername.objectname. For example, select * from db1.dbo.table1
People has different opinions whether they should place eggs into one large basket or into many small baskets. Same in database design too.
August 20, 2003 at 2:38 pm
quote:
Could you please give me some insight/examples of how to write a stored procedure that joins data from a table in one database with data in another database?
Like Allen said, use the fully qualified name:
SELECT
d1.Field1 AS "Database 1 Field 1"
, d2.Field3 AS "Database 2 Field 3"
FROM MyDatabase1.dbo.MyTable d1
INNER JOIN MyDatabase2.dbo.MyTable d2
ON d1.PrimaryKeyField = d2.ForeignKeyField
quote:
Also, can you expand more on the "Controlling" application database?
A "controlling" application is simply an application database that your team or your business thinks is the most central to the organization. The application database doesn't literally "control" anything, it simply is the only database that has tables which store central or common data to other application databases.
--
It must be noted that there are many different opinions on this subject and there really isn't one right answer. The correct answer for you will be one that feels right to you, one that you think will be easiest for you to administer and/or develop applications with, etc.
--
Here's a thread you might be interested in:
Edited by - jpipes on 08/20/2003 2:38:19 PM
August 21, 2003 at 7:27 am
The bounds of a database should be determined by that data which needs integrity between its various elements. This is for disaster recovery purposes, if for no other reason. For instance, if it becomes necesssary to recover employee information to a specific point in time by restoring database backups and logs then most likely all information related to the employee should be recovered to that point in time to maintain integrity. However, if data, like product inventory, is kept in the same database as the employee data and it becomes necessary to recover the employee data to a specific point in time than the product inventory data would also be restored to that point in time, which most likely is not what is desired.
As far as stored procedures go, we try to avoid using stored procedures for those application we wish to be portable across DBMSs since stored procedures tend to tied you to a single DBMS. We have found most performance problems we have encountered are due to poor database design (lack of data normalization) and performance improvements gained by improving database design outweigh those gained by simplying putting queries into stored procedures.
There is an advantage to using stored procedures if most of the logic of an application can be delivered using stored procedures; application logic (that logic which resides in the stored procedures) can be changed without having to redistribute the application. Also, if stored procedures are utilized properly the amount of data being passed between the database and the application can be reduced allowing for better network utilization.
August 21, 2003 at 11:00 am
Database Design/implementations lot of times comes down to the culture and style of the oragnization and DBAs. Having 400 tables makes it cumbersome to model it and change management. It may be a good idea to oragnize into mutiple databases based on the applications which use the similar data. If it can not be divided because of relationships and dependencies then organizing into FileGroups and following a standard naming convention makes it more manageable. The Goal of a good design should be to create database which can deliver better performance, highly available and easily recoverable and manageable.
Good Luck in convincing your new employer.
-Sravan
August 21, 2003 at 11:14 am
Thanks, to everyone that responded. You have been very helpful.
August 22, 2003 at 8:43 am
Another question along these lines. Is there any issue with updating data between databases using a trigger?
Thanks,
Corinne
August 22, 2003 at 8:52 am
A trigger is a special type of stored procedure that is not called directly by a user. When the trigger is created, it is defined to execute when a specific type of data modification is made against a specific table or column.
August 22, 2003 at 8:55 am
Thanks for responding Allen,
I still don't understand if a trigger can be used to update data in multiple databases. Can you elaborate on this? Any examples would be helpful.
Thanks,
Corinne
August 22, 2003 at 9:30 am
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'reminder' AND type = 'TR')
DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail 'MaryM',
'Don''t forget to print a report for the distributors.'
GO
Above is the example from BOL. It creates trigger on table 'titles' for changes in 'pubs' database and calls stored procedure in 'master' database. What you need is to use the fully qualified name.
August 22, 2003 at 10:29 am
While triggers across databases are sometimes useful, remember that one bid advantage to having all your tables in a single database is that CONSTRAINTs cannot cross database boundaries. The reason for this is sysconstraints system table is specific to a database; meaning: a copy of master's sysconstraints schema is created when a database is created on the server. So, you cannot enforce referential integrity across databases. This, probably above all other reasons, is the best reason to consider using a single database. A CONSTRAINT is more efficient and secure than a trigger-implemented integrity check because it occurs before the data is updated and can stop an update from occurring upon the constraint check validating. So, until MS decides to put a dbid column in sysconstraints and re-architect how constraints are confined to a single database, I'd highly consider using a single database if referential integrity is of the utmost importance.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply