Loose coupling among objects within oracle schema

Question

I am building an information service that manages Suppliers. The suppliers are used by our billing system, tender system and sales system. Though 60% of the attributes of supplier are unique to each system, there are still 40% attributes of Supplier that are shared across the systems.

My objective is to build a flexible system, so that change to one individual system's data, should not impact other systems. For example, if i need to make certain tables offline for upgrading them, it should not impact rest of the systems that need supplier information. What is the best way of achieving this? Should all the different context specific attributes live in one schema, but deployed on different table spaces? Also, the read and update may happen more for one set of attributes than the other. How should i logically represent them via one model, but deploy them in such a fashion that they can evolve independently?

Thank you.

Answer 1

First, tablespaces are a means of controlling the storage characteristics of segments, they won't help wrt avoiding impact from changes.

I recommend you create separate child tables for each set of attributes, each with a 1:1 referential integrity constraint to a parent table. e.g.

SUPPLIERS (supplier_id PK, common attributes...)

SUPPLIER_BILLING_INFO (supplier_id PK, billing attributes...) + FK to SUPPLIERS

SUPPLIER_TENDER_INFO (supplier_id PK, tender attributes...) + FK to SUPPLIERS

SUPPLIER_SALES_INFO (supplier_id PK, sales attributes...) + FK to SUPPLIERS

Obviously they'll need to live in one instance. Whether you put them in one schema or in separate schemas is up to you.

Changes to one system should have no impact on other systems, as long as they don't all refer to all the tables (i.e. the Billing system should never access SUPPLIER_TENDER_INFO).

Answer 2

This sounds like a very difficult question that can't be easily answered here. But I can think of a few tricks that might help you with some of your issues. It is possible to make huge changes to your data and still keep the system online.

DBMS_REDEFINITION allows you to change your table structure while other people are still using the table (although it looks very complicated).

Partitioning also allows you to change part of your table without affecting other users. For example, you can truncate just one of the partitions of a table. Partitioning also allows you to use different physical structures for the same table. For example, one partition could use a tablespace with a small block size (good for writing), and another partition could use a tablespace with a larger block size (good for reading).