Problem solve Get help with specific problems with your technologies, process and projects.

One consolidated schema or four separate ones for each app?

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

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.