February 25, 2003 at 10:23 am
Hi guys,
I have a master database and child databases.
1. The master database:
Includes many tables that are needed by every child database. For example; Data code tables, Users demographics and passwords, edit rules, data dictionaries.
2. Child Databases:
Each child database is identical, in structure, tables and tables' names, to the other child database. The only thing that differs from one child database to the other child database is the data that is contained by each table.
My question is what is the best practice in creating Procedures and Views. Since all procedures and views will be identical in everything but the child database name, should I create them in every child database? Or should I put them all in the master Database?
Putting in mind, the Procedures and Views will be modified regularly, the number of child databases might increase or decrease from time to time. ALSO, each child database will be accessed by a unique user, through Web user interface, to update data and run procedures and views.
February 25, 2003 at 11:15 am
I've got a setup like this at work, procs and views in each child db. Works fine, just takes a little extra effort to deploy changes.
Andy
February 25, 2003 at 11:21 am
Thanks Andy, at first I thought putting all Procs and Views in the master db will save me time in deploying changes. But the more I get deep in this project the more I think your idea more efficient.
February 26, 2003 at 6:07 pm
1) I (virtually) never put anything into the Master database. You mess up something here and you'll need to update your resume ;->
For the same reason, I don't let any login use Master as the default database.
2) If you script the objects, it shouldn't be a lot of work to roll them out to the various databases.
I use a script template (SQL 2000) file that already contains logic to detect if the object already exists and then either creates it, deletes and re-creates it or modifies it depending on the project requirements.
Steve Hendricks
MCSD, MCDBA
AFS Consulting Group
(949) 588-9800 x15
Steve Hendricks
MCSD, MCDBA
Data Matrix
shendricks@afsconsulting.com
(949) 588-9800 x15
February 27, 2003 at 8:12 am
Thanks Steve, I really did not mean the master db that is shipped with SQL2000. It is just a user-defined db that acts like a master db since I included all data dictionaries in it. In my project, for example, I work for a Healthcare organization and we need to allow different Hospitals and Departments within the organization to store, process, query and generate reports that are related to their patients. Since the type and structure of the data needed from each Department is the same, I created a child database for each hospital / department. Then, I created a master database that includes a list of tables that will be common for all other child dbs.
February 27, 2003 at 8:16 am
I think Im in the minority, but I see nothing wrong with adding objects to master or having logins default to master (all of mine do). On the objects though, my goal is to make the mdf portable - so if I need to move it to another server I just move it and the login, Im done, don't have to track down which of many objects in master might apply.
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply