CODEWITHSUNDEEP
oracle-databasedatabase-designoracle11g
Jaqen H'ghar
Jaqen H'ghar

Reputation: 4345

"Inheritance" on schema level in Oracle database

I'm working in a company managing several types of publications. There are maybe 15-20 different types of publications, a bit simplified they all consist of a document (pdf) and multiple different attributes i.e. some types have authors, some have other types of authors, some have maybe physical dimensions etc. etc. Until now these types have all had their own schema, and they have all had their own java web application connecting to this schema.

But now it has been chosen to use the following database architecture:

    Webapp A                           Webapp B                      Webapp C
        |                                 |                              |
        ▼                                 ▼                              ▼
Schema A (for type A)           Schema B (for type B)          Schema C (for type C)
        |                                 |                              |
        |                                 |                              |
        ▼                                 ▼                              ▼
------------------------------------------------------------------------------------
|                                                                                  |
|                           SCHEMA X (COMMON PARTS/TABLES)                         |
|                                                                                  |
------------------------------------------------------------------------------------

Common parts go to schema X. Webapp A connects via Schema A which accesses the common parts in Schema X via views (owned by Schema X). Here by webapp A can never see the publications relating to the other webapps in schema X. The webapp-specific tables will go into Schema A, B, C etc.

Above is just for illustration, there will with time be many webapps. In Schema X there are maybe 50 tables.

When webapp A needs to create/update an entity it will also happen through views, meaning that we have maybe 100 insteadof-triggers just for webapp A.

Reason for the new architecture is: everything is only saved in one place, it will be easier to expand the common tables, and it will be easier to export the publications.

I don't have that much experience with database design, but to me the chosen solution seems to be trying to implement some kind of inheritance. We have the common parts in schema X, and the specialized parts in the other schemas. I see there are several ways to achieve inheritance, for example here, but I think our solution is different as it seems to work on the schema level.

So the question: Do anyone have experience with something similar? Pro or against? Maybe this is even a completely normal way of doing database design?

For me, as a java developer it seems extremely complex. Even just to add a single property to an entity is complex. From the small amount of experience I have with it it seems very opague and difficult to follow the dataflow down the layers, not to mention figuring out what's wrong when it fails.

I'll be really grateful for "views" on whether this is a good pattern or an anti-pattern, and for potentially another simpler solution.

Upvotes: 0

Views: 235

Answers (2)

Walter Mitty
Walter Mitty

Reputation: 18940

The different types of publications sounds like a classic case of types and subtypes or, if you prefer, classes and subclasses. Standard SQL has no mechanism for inheritance, but the problem is well understood. It's been seen thousands of times before.

Take a look at the following tags:

The questions, the answers, and the tag wiki will help you with a generic solution, and you may be able to tailor one of these solutions to meet your needs. You can see the tag wiki by clicking on "learn more".

Shared primary key isn't about inheritance as such. It's about enforcing a 1-to-1 relationship that's optional on one end.

Upvotes: 1

APC
APC

Reputation: 146219

Without knowing more about your scenario it's really difficult to give positive advice. I would agree that it seems over-complicated (50 tables in schema X??!) but there may be good reasons for that. It's also hard to understand why you still need tables in the individual schemas as well.

One thing I would suggest: if you have Oracle Enterprise Edition check out Row-Level Security. VPD could give you the data separation you need with a lot less complexity. Find out more.

"for you it is not a design from the standards shelf"

These days very few database designs hail from Planet Standard. But if you have ten thousand tables then you're not even in the same solar system. That is a ridiculous number of tables.

Upvotes: 1

Related Questions