[Q]: What's your experience with multi-app data model development

Hi there.

I'm currently working on a smallish (IMHO) database system. This database system is comprised of 3 integrated applications with approximately 100 tables between them.

Considering the location of these tables, remembering they are shared between 3 applications, what is the best way to implement them:

1. All in a single user schema
2. In separate user schemas

Problems I see with #1 are:

• The tables aren't logical split into application groups. Becomes difficult for the next developer to understand which tables belong to which applications. Some sort of table_name prefix/suffix by application might solve this problem. Personally I don't like doing this because applications can change their names or if you use abbreviations or acronyms they can become forgotten over time and provide no real clue to what application the tables belong to.
• If later on each application grows so large that it becomes necessary to migrate them to separate machines, a single schema doesn't easily allow this (ie its easier to export a user schema than several tables).

Problems I see with #2 are:

• The data model isn't as tightly integrated as it could be. Yes its easy to move user schemas between Oracle instances, but the benefit of a single integrated data model is lost. 2 user schemas is just like 2 different databases where if bad development occurs you can end up with data synchronisation problems between (for example) two tables of customers.

And yes, there may be more problems I haven't listed here with each solution....

I guess one other point I can make here is, yes you can document your solution. I'm a great believer of documentation. But what I'm not a believer of is that documentation will survive the life of the system. So I do not make the assumption that future developers can RTFM.

So after this long spool, I was wondering how developers at large Oracle sites address this problem? What other issues do you try and address in this situation. Can U think of anyother solutions?

Any help would be greatly appreciated. Being a single isolated Oracle developer at my site, I don't get the chance to discuss this problem with other Oracle developers in order to share experiences.

If you do reply could you please email your comments to the address below. Take note of the instruction below.

Regards & thanks for your time :)

Chris Muir
Chris.Muir_at_no.spam.mitswa.com.au

If you wish to email, please remove the 'no.spam.' component in the above address. Thanks :) Received on Thu Feb 04 1999 - 02:05:50 CST