Advice on setting up a central db with master tables for web apps

I'm starting to write more and more web applications for work. Many of these web applications need to store the same types of data, such as location.

I've been thinking that it may be better to create a central db and store these "master" tables there and have each applicaiton access them.

I'm not sure how to go about this.

• Should I create tables in my application's db to copy the data from the master table and store in the app's table (for linking with other app tables using foreign keys)?

• Should I use something like a web service to read the data from the master table instead of firing up a new db connection in my app?

• Should I forget this idea and just store the data within my app's db?

I would l开发者_如何学Cike to have data such as the location central so I can go to one table and add a new location and the next time someone needs to select a location from one of the apps, the new one would be there.

I'm using ASP.NET MVC 1.0 to build the web apps and SQL 2005 as the db.

Need some advice... Thanks!

This is a pretty common question and the best way to handle this is to ask yourself the following question:

Will I ever need to reuse this data across another application?

If the answer is yes then you should definitely design your table so that it is separate from the actual application. Let me give you an example. Assume you will be writing an application to log helpdesk tickets for your company's employees. One thing you notice is that employees are already stored in a common database called AcmeEmployees.

Your goal as a programmer / dba is to avoid duplication, administration, and typographical errors. So you only want to administer data at most once. It makes sense for your helpdesk application to reuse employee information that is stored in another database.

If you write a stored procedure and you want to access this employee data you simply do:

SELECT EmpFullName FROM AcmeEmployees.dbo.Employees

That is databaseName.ownder.tableName. So yes design with the idea in mind that you want to reuse common data. Here are some benefits to doing this:

• Inserts / Updates are done once and only once
• Little to no typographical errors (if it's done correctly once you never have to worry)
• Any change to the source data is automatically seen across your entire domain (all applications that use this common data)
• Reusability means less people doing clerical type work and more time to look at other issues at hand.
• Fields such as Status can impact an application, for example, in the helpdesk system I mentioned above if an employee decides to leave the company setting that employee = inactive will ensure that employee is no longer able to log tickets in the help desk system. It is a good means to keep old data out of the new systems
• And the best one, it just makes sense, in my workplace we have so many different applications but the nice thing is we have a set of common data tables. One of those is employees. I would hate it if we had to re-create an employee and set a first name, last name, middle initial, etc. every time we got a new employee and we had to insert that employee in each app.

Let's continue with our employee's example. HR now is the master department updating or inserting employee information. I can then reuse this table to create not only my helpdesk tracking system but say a manager wanted an org chart. He / she wants some sort of organizational hierarchy listing managers and the employees that report to their managers. Well great you think, you have a common database table "Employees" which has fields such as EmployeeName, Status, ManagerID. You can then quickly build an org. chart application using this same common data:

SELECT ManagerID as TheManager, EmployeeName
FROM Employee
WHERE Status='Active'
  AND ManagerID="SomeManagerID"

Now you've got a set of employees reporting to a manager and you can build a hierarchy based on this. Well what is the point of this second example you may ask. It means that your development becomes MUCH FASTER. You stop worrying about designing another database and you reuse what is common and available to you. It speeds development!

Depending on the data, how complex it is, whether you use it in relational terms and so on you will want to either:

1. Just store it in the app's database. If it's replicated but static, it really doesn't matter; or
2. Store it in memcache or a similar in-memory cache and have either one instance used by all apps or multiple as appropriate.

Well, that depends on how much work you want to do :-)

One one case, I've seen it done with a central database table in SQL Server. When queries read the data, they can read it like:

select * from CentralDB.dbo.RefTable

So you can place all of your procs within your database, but read the data from the central DB, if you want, that way you can manage one DB.

Alternatively, if you do share the application data, another approach I've seen taken is to have a batch process migrate data from a shared common DB to each individual DB, so that way you still have one master copy, and the data gets pushed out regularly.

You have to work out though, when a value changes for one app, how is that handled for the others. Should it automatically appear, or shouldn't it. That's OK for things like US states, zip codes, school districts, and the like, but could be burdensome for other types of data.

HTH.