Structuring a PDM

[former_member217396]

Hi all,

quick question.

We have the following case

- in the database (Oracle 12c) we have two schemas: SCHEMA_A and SCHEMA_B

- 90% of the objects in both schemas are same

- the other 5% of the object exists only in one of the schema

- the last 5% of the objects exists in both scheman, BUT there is a difference in the attribute list. F.e. TABLE_1 in SCHEMA_A has 3 attributes: Attribute_1, Attribute_2, Attribute_3, the same TABLE_1 in SCHEMA_B has 4 attributes: Attribute_1, Attribute_2, Attribute_3 and Attribute_4.

Now: since 90% of the objects (and this are hundreds of objects!) are same, the other 5% are similar (there is only a difference in the list of attributes and maybe (worst case) indexes or constraints), the idea is, we will package the model in the following way:

1) we will create a MASTER-Package, where we will create the Objects -> TABLE_1 with all the attributes

2) we will create ExtendedAttributes (of type Boolean), one for each schema, so SCHEMA_A and SCHEMA_B

3) we will set the ExtendedAttribute to TRUE to the attribute, where we know, it should appear in the SCHEMA_A or SCHEMA_B

4) we will create another two packages SCHEMA_A-Package and SCHEMA_B-Package

5) we will replicate the objects from MASTER-Package to SCHEMA_A-Package and SCHEMA_B-Package according to the state of the ExtendedAttribute.

So... at the end, we will have one master object in the MASTER-Packag and replicas in the "child" packages SCHEMA_A-Package and SCHEMA_B-Package containing only the relevant attributes.

Some of the options, like Owner will of course will be de-replicated, and set to correct value during the replication.

This seems to work. I have build it and tested it. The only issue I found is during the reverse engineering, is that when I wanted to reverse engineer the objects from SCHEMA_A and SCHEMA_B, I had to reverse it into the MASTER-Package, and run an procedure, to consolidate it into one object (and set of course the ExtendedAttribute correctly).

Does anyone knows any other drawbacks of using such an approach?

So this is how it looks like at the end:

Thanks!

Rafal

[Former Member]

Hi Rafal,

I used this approach some time ago for very similar case. Data models for more countries, 95% of the model is the same, 5% national specifics different for each country. Before that, there were only boolean extended attributes used to distinguish, to which country the table column (or index) belongs to. From my experience, this approach can be useful for one-time big bang transformation, but it is not sustainable in the long term. The users often forget to set it correctly so it is not appropriate approach.

Our solution also allows to generate separate data model 100% for certain country from this MASTER common model, applying those national specifics (contained in package) over the master objects in the root level.

Regarding the replications, examine thoroughly, whether the replication granularity is good enough. For example ext.attributes or extended collections can be de-replicated only as a whole. Also not each standard attribute can be de-replicated.

Reverse engineering will be of course more problematic, but there is no better way how to manage such environments with 90% of the same objects.

Ondra