Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Orlando.

Asked: January 04, 2010 - 4:26 pm UTC

Last updated: January 18, 2010 - 4:38 pm UTC

Version: 10.0.1

Viewed 1000+ times

You Asked

Microsoft architects says that Object-Orienteted models is the best practice for abstractions of ERD's. For ORACLE developers the Entity-relationship models represents the real-life. In your experience, and for Oracle Database and applications wich practice is the best and why?

and Tom said...

Are you building a single application that will be the sole user of that data forever and ever - nothing else will ever want to use it, the data never existed before and will only be used by your application - it'll never be extended to be used by some other application - and your application will be so awesomely coded that it will still be running 10 years from now?

If you say yes, I'll just smile and walk away, because I know the answer is "no, of course not"

Applications come
Applications go
data lives forever.

Think about that for a while - Microsoft or Oracle - not sure why the distinction.

If your goal is to build a stove pipe with a limited life span - create everything as objects. And be prepared to live with the performance implications of that (fast to whip up something, not so fast to return the ad-hoc data request, not so fast to change). Be prepared also to be supporting that for decades to come (hah, right, in five years the way we program today will be so "old fashioned").


To me, it is all about the data, you can put an object model on top of the data if you want, but you should start at....

the data.


I'm not even really sure how you would do this though:

... that Object-Orienteted models is the best practice for abstractions of ERD's...

if you have an ERD, why would you "abstract" it into something O-O and even more importantly - "how" would you do that?

Rating

  (22 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

data lives forever

Sokrates, January 04, 2010 - 5:09 pm UTC

that's pretty obvious, isn't it ?
it's the meaning of "persistance"
how can one be so blind not to see that, I often wonder.

Tom Kyte
January 05, 2010 - 8:25 am UTC

look at the job title:

application developer
^^^^^^^^^^^


we used to call them data processing professionals, but data processing became an insulting term I guess at some point.

ERD

A reader, January 04, 2010 - 6:23 pm UTC


Oleksandr Alesinskyy, January 05, 2010 - 7:32 am UTC

Not all data live forever - at least in OLTP system. Migration of "outdated" data to a warehouse is quite common process. And an warehouse may have quite different structure.

As for modeling - if objects (entities) have a really rich behavior O-O model is suitable, if a main purpose of your application is a more-or-less simple CRUD (the most common scenario, by the way) then a story is different.

So, as almost always, it depends ...


Tom Kyte
January 05, 2010 - 10:27 am UTC

disagree with you... Your example is why actually.

In most every transactional system I've seen - the data does live forever - in the transactional system.

You know why? Because at "application design time", no one gave any thought to the data at all - they don't build archival processes into the database. They organize the data in such a fashion as to make archival near impossible - if not impossible. If you don't actually organize your data to be archived - guess what WILL NOT happen to it? It won't be archived. It'll grow and grow and grow.

One of the most frequently asked questions I get is "how can we delete X records out of Y - we didn't think it would grow so big so fast".

If you design the data for your application - your pitiful, small, tiny, short lived application that won't be here in 10 years (but hopefully your transaction processing needs still are - or you are OUT OF BUSINESS) - you will end up rebuilding it over and over and over again as every change is needed, finally scrapping it altogether.


If you are building a CAD/CAM tool - maybe an O-O data model is a good idea, you don't typically need to ad-hoc, change the way you use that data.

Transactional data - everyone wants to slice it, dice it, chop it up, repurpose it, in short

USE IT


So, I disagree. As always - it does depends, but it is so so rare as to not be something most people ever encounter.

Yes, data lives forever

Enrique Aviles, January 05, 2010 - 8:53 am UTC

Oleksandr,

The "outdated" data that is moved to the warehouse lives there. It is relocated from the OLTP database not deleted or purged. Even if it is archived on tapes, it still lives somewhere. It might not be live data but it lives somewhere else.

Has anyone run into a situation where managers don't want to permanently delete data "just in case we need it"? They may need it for legal reasons, historical analysis, etc. but there is an uneasy feeling about deleting business data. The application/latest fad will change but the data remains, that's what defines the business.

Oleksandr Alesinskyy, January 06, 2010 - 4:29 pm UTC

Even if data are kept in the transactional database because nobody have foreseen their deletion (for me it more or less the same as "we obtain such funny results because somebody has not foreseen that 2+2=4") they are of no interest as far as they are copied to DWH.

So if I need a really new version of my application (that means with major change in requirements) I am free to develop a new DB structure for it and migrate a moderate amount of data to this new structure.


Tom Kyte
January 07, 2010 - 7:21 am UTC

.. becauase nobody have foreseen their deletion ...

No, it is

because no one thought of the data, it was all about pretty screens - oohhhh, ahhhh




... they are of no interest as far as they are copied to DWH. ...

sure they are, why aren't they? How can you say that. And just because you say it out loud doesn't make it true.


... So if I need a really new version of my application (that means with major change in requirements) I am free to develop a new DB structure for it and migrate a moderate amount of data to this new structure. ...

and you have missed every single point being made at this point.

Who is to say "moderate", what if you need it all?
What if the DATA downtime is to be measured in milliseconds whilst doing that?
What do you do with that mess of data that has no integrity (because the application that used to be cool did it all)?

You are proposing to continue and compound the insanity that plagues our profession, the 'never time to do it right, but always time to do it over and over and over and over'.

The problem is people are building *horrible* data applications that abuse and misuse the data, others make decisions based on this bad data, the problems multiply - and the answer is "no worries, version 2.0 will be so much better"



Try to take something like say..... <company name redacted, but it applies to most any company really> (which still runs really really old - I mean older than my daughter old - commercial software because upgrading is too too hard, too many connections between systems). Your application is NOT a stove pipe, it will be used for a feeder system to someone else, it might go to a data warehouse (about which I care not a whit), but if you are creating/managing data of any relevance - you will be a feeder system for someone - and someone else will feed you. A zillion "stovepipes" that once you get in place - become immovable obstacles to any more forward progression.

"no, it is too hard to change that now"

because you built applications, not information.

@Enrique Aviles

Oleksandr Alesinskyy, January 06, 2010 - 4:31 pm UTC

Yes, they live somewhere - and what? As far they are not in my DB, they are as good as non-existent for me.

Tom Kyte
January 11, 2010 - 7:58 am UTC

but you keep missing the point that...

unless you did a data design, not an application design, they will live with you forever - applications that were not built to "purge", won't purge. I see it every single day "how can we purge..." - my answer "very painfully, slowly"

If you build an application, that is what'll you''l get. Most people however really want a system.

You are missing the point

Enrique Aviles, January 06, 2010 - 7:31 pm UTC

Oleksandr,

The point is not if *you* think the data exists or doesn't exist. The point is that in 5, 10, N years down the road, when nobody gives a hoot about version 1.0 of your application, "outdated" data is still relevant. Again, many times management doesn't want to permanently delete anything which proves data is invaluable, hence:

Applications come
Applications go
Data lives forever


@Enrique Aviles

Oleksandr Alesinskyy, January 07, 2010 - 8:04 am UTC

Whatever you think on it - outdated data should not exist in transactional processing database. In many cases we are legally obliged to remove them keeping aggregated/anonymized data (and there is no sense to keep them in transactional DB).

If somebody makes design error - the worse for him. Concerns shall be separated.
Tom Kyte
January 11, 2010 - 8:14 pm UTC

We agree Oleksandr

what the issue is - that is DOES

why does it?

because someone built an application with absolutely NO THOUGHT towards the data, none.

and guess what? that architect? that 'designer', they will have moved on, it'll be someone else's problem.


A design error is designing applications with no thought towards the data.

The funny thing is - I think we actually agree 100%, but you know to design to the data - to do a data design. Most people do not.

The crux of the problem

Kevin, January 07, 2010 - 8:45 am UTC

And I don't mean the original question - but with the response of "If its not the current data its not my problem".

One of the things that I have to struggle with on a constant and daily basis is just that attitude. The "Throw it over the wall and let someone else worry about it" syndrome.

As database professionals ( and this SHOULD include application developers as well as DBA's ) the focus and concerns should be about the system as a whole. How does it meet the users needs, how can it be maintained and enhanced, how can the data be protected, how can it be leveraged in new ways that can make sure we have a job tomorrow.


Tom Kyte
January 11, 2010 - 8:17 pm UTC

exactly....


I don't call us database professionals, I go further and try to insult all of us.


We are data processing professionals - or at least should be. DATA PROCESSING. that is what we do. I know it is insulting to some people. But face it.

We
do
data
processing.

we do data processing.

we are data processing professionals.

We are not application architects.
We are not web 2.0 (or n.m) gurus.

We are data processing professionals :)

Transactional data does "live forever"

Stew Ashton, January 07, 2010 - 9:44 am UTC


The Seine river flows through Paris - wait, that's not the same water as last year! OK, but it's still the same river.

My transactional data base - wait, it has this year's data, not last year's! OK, but the conceptual and physical models and integrity constraints are the same, so it's still the "same" data, it's just that the data is "alive".

Not only does data outlast applications, but it also outreaches applications. As Arup Nanda says, there should be "one database for many applications; not tiny databases all over the place." (comment on Tom's blog).

So what happens if that database is accessed from COBOL, Java and PHP? Is there a common O-O paradigm for all current languages? No, but every language can handle result sets.

Data really might be forever

Chuck Jolley, January 12, 2010 - 8:32 am UTC

I have absolutely no idea what kind of system these records were created on.
We have been off our last mainframe 10 years and these were at least 2 mainframes ago. 
And yet, they still live for some legal reason or other.

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 12 08:08:42 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> @connect chajol@tax11g
Enter password:
Connected.

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
HOST: ******************
SERVER OS: x86_64/Linux 2.4.xx
chajol@tax11g>select ty.tax_year,
  2         to_date(substr(p.receipt_no, 2, 6), 'mmddyy') pdate,
  3         p.receipt_no
  4    from tax.accounts a,
  5         tax.pay_lines pl,
  6         tax.payments p,
  7         (select min(tax_year) tax_year from tax.tax_years) ty
  8   where a.tax_year = ty.tax_year
  9     and pl.acct_id = a.acct_id
 10     and p.payment_id = pl.payment_id
 11  /

TAX_ PDATE     RECEIPT_NO
---- --------- ------------------------------
1977 03-JAN-79 00103790016
1977 15-NOV-79 01115790004
1977 16-NOV-78 01116780009R

3 rows selected.

Elapsed: 00:00:02.10

chajol@tax11g>


@Chuck: watch out!

Stew Ashton, January 12, 2010 - 10:20 am UTC


Suggest you change that password, which is now visible to all but the Na'vi (or was that a "virtual" password?)

Also your version of SQL*Plus is older than your DB ;)

(lucky devil being in 11G anyway...)

OOPS ! My Mistake!

Stew Ashton, January 12, 2010 - 10:24 am UTC


There was no password, what was I thinking???
SELECT 'Stew' DUMMY FROM DUAL
/
DUMMY
-----
Stew
Sorry about that.

;)

Chuck Jolley, January 12, 2010 - 12:32 pm UTC

My PC has the 10g Windows client.

Confession: We aren't actually on 11gR2 yet.
But this will be the production server in about three weeks.
And luck had nothing to do with it.
Just a lot of work. (fun work)

The point of course is that the people who wrote the main frame code that created those records could not have in any way envisioned the Oracle for NT 7.3 relational database they were converted to in 1998, let alone the 64 bit Linux 11g server they are going onto next.
Look at the dates. Those records were sitting in VSAM, or something for TWENTY YEARS before they even went into an Oracle database, and that was over ten years ago!
Who knows what will be storing our current transactions in twenty years.


And the answer is...

Enrique Aviles, January 12, 2010 - 2:46 pm UTC

"Who knows what will be storing our current transactions in twenty years."

Easy, Oracle release 20!!

But, there will be a fancy new programming language running a cool OS on very fast hardware. Java and OO paradigms will be a thing of the distant past :)


You forgot the details

Chuck Jolley, January 12, 2010 - 3:18 pm UTC

"Easy, Oracle release 20!!"

You forgot the details:
Oracle 20h Holographic Relation Server R2
Running on OEL-14 for analog living brain cells.

And that new programming language will still be wonderful or suck depending on how it's used.


EM will still be 10g though... ;)

Transactional data does "live forever"?

Oleksandr Alesinskyy, January 14, 2010 - 4:08 am UTC

The Seine river flows through Paris - wait, that's not the same water as last year! OK, but it's still the same river.

But 500 years ago it was drinkable - but not now.

My transactional data base - wait, it has this year's data, not last year's! OK, but the conceptual and physical models and integrity constraints are the same, so it's still the "same" data, it's just that the data is "alive".

If you DWH database has the same physical model as your transactional database - then the most likely it is unusable.

Moreover DWH database rarely has the same conceptual model and integrity constraints as transactional database(s) from which it is populated - business rules and data evolve over time. Just a couple of examples

Many countries introduce mandatory identification numbers, tax payer identifiers and alike. So new data about financial transactions must have them - but old data must not (and may not).

Many countries (e.g. most if not all EU countries) have privacy restrictions - after some (relatively short, typically below 1 year) period of time data must be deleted or anonymized (that means detached from the individual). That means that data in DWH would differ from transactional data.

And so on.
Tom Kyte
January 18, 2010 - 4:38 pm UTC

you keep missing my point.

In order to move data out of your transactional system - what need you have done FIRST?

did you need to think about the user interface?
did you need to consider what framework to use?
did you need to consider how to get the awful SQL stuff generated?
did you need to think about how the end user would like your application?

No, you needed to think about.........


The data - and how to organize it to PERMIT (just to even think about permitting) a purge to happen. And you need to think about how to organize it to do this efficiently.


and there are many transactional systems that feed the warehouse - but CANNOT PURGE, the data has to stay there due to regulations - has to.



And you know what, every "and so on" leads us back to "you better do a DATA DESIGN, not a myoptic 'my application is so so cool' design" - else you will have utter garbage in your database that you cannot

a) anonymize
b) delete from
c) archive from

eh?

Chuck Jolley, January 14, 2010 - 8:19 am UTC

But if your transactional database can be screwed up by your application then those mistakes will just be propagated to the DW and live THERE forever.

more...

Chuck Jolley, January 14, 2010 - 8:21 am UTC

And you won't have the original transactions to fall back on.

@Oleksandr re: transactional data

Stew Ashton, January 15, 2010 - 6:33 am UTC


Wow, your interpretation is exactly the opposite of what I meant to say. I wasn't talking about Data Warehouses at all. I was saying that even in databases where the actual values are volatile, the structure and the constraints are pretty stable.

When thoses values migrate elsewhere, they may move into different structures, yes. Meanwhile, the constantly changing transactional data has a stable "identity" which generally lasts longer than the applications written on top of it.

eh?

Oleksandr Alesinskyy, January 18, 2010 - 6:21 am UTC

But if your transactional database can be screwed up by your application then those mistakes will
just be propagated to the DW and live THERE forever.


Sure, almost 100% agree (not 100% because mistakes may be caught during ETL), but how your statement is related to mine? I do not see any connections between them.

I have essentially stated "requirements for transactional application and related data structures change much more often then for DWH. A properly organized transactional application should keep only live data, so its migration to the new schema is not so undo-able." - but I never have said "develop bad data structures, spare on constraints, decentralize logic that may be centralized ... and so on".

@Stew Ashton

Oleksandr Alesinskyy, January 18, 2010 - 6:24 am UTC

Meanwhile, the constantly changing transactional data has a stable "identity" which generally lasts longer than the applications written on top of it.

Identity - yes, but not the structure and constraints (otherwise a new application would not be required).