February 17, 2009 at 1:33 pm
I've been wondering recently,
if you have applications that reference the same set of data but have their own subsets of information that they use to run what would be the best way to design and manage that data
for example,
if you have a users database and use it to authenticate for your 4 client applications, each having their own subset of application nuances stored in databases. what would be best?
to store the common data in one database then have everything that application needs to run in it's own database, but still reference the central hub of information in the users database?
also if you have another application that manages the central hub's information? should you store that information (picklist, etc.) in it's own database yet again?
where do you draw the line between function and consolidation?
February 18, 2009 at 10:42 am
sandman8301 (2/17/2009)
I've been wondering recently,if you have applications that reference the same set of data but have their own subsets of information that they use to run what would be the best way to design and manage that data
for example,
if you have a users database and use it to authenticate for your 4 client applications, each having their own subset of application nuances stored in databases. what would be best?
to store the common data in one database then have everything that application needs to run in it's own database, but still reference the central hub of information in the users database?
also if you have another application that manages the central hub's information? should you store that information (picklist, etc.) in it's own database yet again?
where do you draw the line between function and consolidation?
I would draw the line between the last n in "function" and the c in "consolidation".
😛
Seriously, though, it's a pretty broad question, and a hard one to answer without more detail.
I've certainly dealt with this scenario fairly often. One consideration is that if the database is transactional, and is updated/inserted fairly often, you would want to set the recovery model to "full" (which gives you the ability to backup the transaction log), whereas if this is a database that is rarely updated, or is perhaps reloaded daily, you may want to use the "simple" (or maybe the "bulk-logged") recovery model.
When separated into different databases, you also have the advantage of having different schedules for backups. An active database generally needs to be backed up more often than a fairly inactive one. Is disk space and backup management a factor?
If you have a main database that needs a few tables from another, it may make sense to create views into the other database that select all columns (or only those that are needed, of course).
We have a few reporting databases, and each uses the same fiscal calendar table, so we just have views that access one fiscal calendar table in the "main" database. Of course, you can just reference the database as database.dbo.table if you prefer.
I think it's partially a matter of taste as to how you "divvy-up" your tables into separate databases. Views can help you to virtually consolidate your tables, but sometimes performace will suffer.
February 18, 2009 at 2:48 pm
i guess the scenario is
we have
1) a web app
2) a installable app
3) an app that manages the client's information
the settings for the clients can be different for each application
and the managing piece doesn't even care about the clients settings.
just their permissions
would it make sense to separate the details of the settings. and application specific things like the values of the picklists in the ui?
i am getting the rare chance to take an existing database structure and basically rebuild it from scratch. so i want to make sure that it's logical and flexible. and if we do away with one of the applications in the future it's easy to separate that data out instead of worrying about the hooks that could be left.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply