We are weighing the pros and cons of building four applications' data on a big consolidated schema (catalog in MS SQL Server) or each application has its own schema. One team insists the centralized approach will be the easier way for sharing common data between four applications. The other team is insists the centralized approach will more complex for tables maintenances especially when come to application version upgrade. Do you have any opinion on this or any white papers that address this debate?
There were a number of papers published years ago on this topic, but I don't have a clue where I'd look for them today. If you check back issues of CACM (Communications of the ACM) and related journals from the early 1980s, you can probably find them.
I would STRONGLY suggest that you keep separate catalogs, one for each application. My reasoning for this is that it is always easy to combine data, but it is normally much harder to separate it once it has been commingled! In MS SQL Server, this would correspond to four separate databases.
These databases can probably all be hosted on a single MS-SQL server. Unless there was some really compelling reason for separate servers (the need for additional hardware to support the transaction load would be the most obvious reason), I'd combine them onto one SQL Server running on one NT server.
For More Information
- Dozens more answers to tough database design questions from Pat Phelan
- The Best Database Design Web Links: tips, tutorials, scripts, and more
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your database design questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
Related Q&A from Pat Phelan
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.