The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Single Database for Multiple Applications

Forget that this may or may not be a good idea, does anyone have any recommendations on books or other material on enterprise database design.

Advice on a business layers, when stored procedures are good or bad, how to keep the database up to date ect …

I need to control the data \ schema across multiple applications and not break any software with updates. I also need the schema to be updated independently of the  applications, and ideally want a “filter” layer that will keep the data clean.

Oh I am using SQL Server if that matters.

Any ideas?
Chris Thompson Send private email
Thursday, March 27, 2008
 
 
This may be too short an answer to be useful, but my initial response is: Hire a DBA.
Odysseus Send private email
Thursday, March 27, 2008
 
 
I am not new to SQL development (I have 10 years experiance with various RDMS) I am however always looking for better ways of working.

I know alot , just not everything!
Chris Thompson Send private email
Thursday, March 27, 2008
 
 
+1000 Odysseus

I worked on a project once that did something like this.  A huge problem they ran into was their data dictionary - the different user domains had different uses for the same terms.

Since none of the user groups (independent agencies) would give up their cherished terminology, the final implementation ended up being six databases - one for common/shared items and domain-specific ones for each of the agencies.  The common data was distributed via replication - from the domain DBs into common & out to the other domain DBs.

There were interminable squabbles about "the other guy"'s input validation & whether or not so-and-so should be allowed to inject 'unclean' data into the common repository. What happened to *that* address for *this* person. Etc. One of the agencies pulled out of the common repository altogether for a while until their boss ordered them to reconnect.

If you're going to do this, I recommend
1) a common data dictionary. Everyone should use *a* term to mean the same thing.
2) some agreement, preferably in writing, between your user groups outlining shared responsibilities & rules (validation, staffing, budget, ??).
3) audit logs. otherwise it devolves into i-told-you-so recriminations.
4) if you end up with multiple databases, a single data center; single machine if possible. It's bad enough if your queries are cross-database but it's a killer if they're in different locations.

Good luck!
a former big-fiver Send private email
Thursday, March 27, 2008
 
 
Refactoring Databases is one.
http://martinfowler.com/books.html

What you're calling a "filter layer" sounds suspiciously like a view.

I concur with Odysseus' opinion.
Peter Send private email
Thursday, March 27, 2008
 
 
Chris Thompson: "I know alot , just not everything!"

Sorry, Chris. I meant no disrespect in my response. What I meant was that what you describe seems like a typical day for a busy professional DBA.

I would let them take care of it, as stuff like that it outside of my (also vast) experience.

By all means learn from them, but don't try to re-invent the wheel. These guys do this stuff all the time.
Odysseus Send private email
Thursday, March 27, 2008
 
 
Views, views, and more views. Just not views on top of views on top of views, please.
anomalous
Thursday, March 27, 2008
 
 
You want to be careful using views in SQL Server. 
In SQL Server you can use 1 instance with multiple databases.  that is a nice way to partition things so schemas don't interfere with each other.
Jim Send private email
Thursday, March 27, 2008
 
 
<< Odysseus
Sorry I did not mean to sound like an arse. You did not offend me in any way.

<< a former big-fiver
Thanks for the tips the audit logs are great idea.

<< Peter
Thanks for the link I have purchased the book.

I guess I could have explained things better, The database is only 50% the problem.
The main issues I see are …
Applications I have no control over accessing the data and integrity must be kept over various applications. These applications must be able to control release dates without the database structure breaking the program. This database will be shipped to 20000 companies and may have ten or so applications using the data.

I can  use views so that the underlying data structure is hidden but I do not see this as a large part of the problem.

I have been thinking of using a DAL that everyone can use as an API for any CRUD functions. The problem with this is that the applications could be written in VB, Delphi , C++, or any .NET. So I am not sure how to best do this.

My second thoughts are to use 100% stored procedures for all single record insert, edits and deletes and to use a dump table for mass updates. Basically a developer writes to a table and I then I check the data and post, edit and delete the correct data. Access Accounts (A large UK Accounts Package) uses this “API Tables” and it seems to work well.
Chris Thompson Send private email
Friday, March 28, 2008
 
 
A crucial thing is to require the applications to use prefixes in their object naming (tables, views, triggers, stored code, etc), so that there aren't collisions.

So instead of customer, supplier, emp, dept, etc, which everyone would tend to have if you use pp_customer, pp_dept, etc. then each application can coexist in the DB and it's easy to see who owns what.
cbmc64 Send private email
Friday, March 28, 2008
 
 
>> I have been thinking of using a DAL that everyone can use as an API for any CRUD functions. The problem with this is that the applications could be written in VB, Delphi , C++, or any .NET. So I am not sure how to best do this. <<

The problem is not the data access methods, or the programming language -- it's differences in the definition of your business entities between the apps:

"Oh, we thought that the LastModifiedOn value wouldn't be updated when an Invoice is paid, as you're not modifying the Invoice, but the InvoicePayment record."

"We need 3 address lines for CustomerAddress, not 2"

"What do you mean you're hashing the passwords on the client using MD5?  You should be doing it on the server using SHA256.  You need to change it now, and update all the programs which access that column."
xampl Send private email
Friday, March 28, 2008
 
 
Also, be careful of ORM frameworks like Hibernate.

In their default configuration, they often like to assume that no other application will ever touch the database, so they use in-memory caches and counters.

Becuase of those fiddly little details, two different applications, connecting to the same database, using the same ORM implementation, and executing queries at the same time might return different results. (Or, in some cases, if the database is updated outside of the ORM, the application will crash. I've seen it happen.)

I think it's usually possible to work around those types of behavior, but you have to really know the framework like the back of your hand.
BenjiSmith Send private email
Saturday, March 29, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz