Database Design Decoupling

Question

Hi,

I am working on a .NET web project with an SQL Server back end. There is a core part of the database that is populated with stock data. This data is loaded daily by a loading application.

This data should remain readonly to the website. By this I mean, that I do not want a Web developer to modify the tables that are are specific to the stock data nor do I want them write data to these tables as this will be overriden by the daily update.

Obviously, there will be many tables that will be required by the website such as authentication, admin, etc. and in some cases they will have to allow the user to manipulate the stock data to do things like => the GOOG should display a stock name of 'UsersGoogleStock Inc.' instead of the stored daily loaded name of 'Google Inc.'.

My question is, should I have two separate databases? One for my readonly stock data and the second for website specifics?

Answer 1

Should I have two separate databases?

Never.

Simply tell the web developers what to update and what they cannot update. Don't compound a simple social problem with needless technical complexity. Talk to them. Explain the rules. It's easier than every other alternative and more successful in the long run.

Answer 2

No, you should have one database. The authentication (username/password typically) used by the Website should simply have appropriate security to control what it can read and write to.

Answer 3

You can grant or withhold the necessary privileges to INSERT, UPDATE or DELETE on a table by table basis, so you don't need a separate database. You could create more than one schema inside the same database to make it clear to all the programmers what they can update and what they can't.

See the GRANT or REVOKE documentation for whatever database you choose.

Answer 4

The three answers given so far have all been against.

To add a for point to the discussion there is a modest performance benefit to setting a database to readonly because of the reduced locking overhead.

Answer 5

I would put it in a separate database because you propbably only want simple recovery on tables that are only populated from an outside source and would not need to be recovered from the transaction logs. It is often a good idea to separate those tables that need only simple recovery from those that need transaction log details for data recovery. This will speed up importing and will make the datbase that needs full recovery operate faster as well because it won't be slowed down by the import.

If both need the same mode of recovery, they can be in one datbases and you can set the tables inquestion so that the developers do not have rights to them. Of course, your developers should not have rights to modify anything in production anyway. Only a dba or a configuration person or a manager should be promoting production changes.