Re: Enterprise Data Model benefits

From: Kenneth Downs <MyUseNetHandle_at_linuxmail.org>
Date: Tue, 13 May 2003 22:18:41 -0400
Message-ID: <df9s9b.k57.ln_at_mercury.downsfam.net>


Quoting unnamed sources, Dean claimed:

> Hi,
>
> Here's a question.
>
> How much (as say a % of development costs) could a company expect to
> save over the medium to long-term (5 to 10 years) if they started a
> process to develop an enterprise data model?
>
> Open-ended and dependant on countless other factors I know, but I'm
> trying to get a grip on the tangible benefits of such a model.
>
> cheers,
>
> Dean.

The short answer is > 0. There are tasks associated with maintaining and growing a database system. If those tasks can be described by data, then they can be automated, improving reliability and reducing costs.

Hmmm, exact percentages are hard to come by, but if you have no such model and therefore no culture surrounding the use thereof, you almost certainly, IMHO, have abundant fat to trim. Percentages are tough, but perhaps some anecdotes from my own experience might allow you to come to some conclusions of your own.

First, you have to define "Enterprise Data Model." Because everything everywhere sooner or later resolves to a column in a table, then this model must also resolve to such.

You would be amazed at how much you can accomplish with a data dictionary of a half-dozen or so tables. You need a table of tables, a table of column classes that defines things like type, size, precision, etc. Then you have a cross-reference that links column classes into tables. Then you store things like defaults and stuff for the tables. Then a list of keys can store a primary key, candidate keys, and normal indexes. You can put in vendor-specific flags like "clustered" for the keys if it suits you. Then you put in a table of relations, perhaps with the assumption that any columns with the same names as the parent's unique constraint columns will be used to match, and allowing for explicit differences. Finally you top it off with the ability to express derivations, such as fields fetched from one table to another, aggregations such as summing detail lines to a header, cross-record derivations such as extended price (qty * price) and so forth.

If you have no "Enterprise Data Model" now, this make a durn fine start, and allows for the following, if you are clever with a host language that can construct SQL on the fly:

-> Self-documenting. Crystal Reports will do a fine job of documenting

   your system for you out of these tables. -> Platform independent. NEVER NEVER depend upon a particular vendor's

   system catalogs. For regular run-time use, consult your own tables. -> Easy upgrades. The only moment when you must depend on a vendor's

   catalogs is when you query the server to compare the Data Dictionary's    version of what should be to the server's version of what is. Then you    generate DDL to bring it current.
-> Also on upgrades, you can generate a plan and report, so that appropriate

   parties can sign off before you actually commit your upgrade. -> Generated triggers. Our systems have no hand-made server-side code, it

   is all generated and applied out of the Data Dictionary. -> Choice of implementation. If you have some derived column, such as

   "Price after Discount", you can decide yourself whether to make this    a physical column that is populated in a trigger, or a -> Make "X" like "Y". Imagine having a dev system and a production system

   and being able to say, "They've signed off on the dev system, now make    the live just like it." Much easier if you can roll out the dictionary    for one and apply it to another.
-> Generalized Extraction (reports, EDI, outbound exports, etc.) can be

   made to jump through hoops. Using the tables described above, a very    simple alogrithm can take the human request "Give me all orders with    no production orders yet that are being shipped to California" and    generate a complete SELECT purely out of a field list, the filter    criteria, and your data dictionary.
-> Generalized Imports (EDI, imports and feeds from other systems) can

   be made very easily. You can specify that a certain file/table/etc    must map into a table. The system then looks at the relations that    that table has and uses them to validate the columns in the input.    (The constraints could do this for you, but you have far more control    on an input if you do it yourself).
-> UI development can be reduced to a completely generated set of code that

   is then hand-tweaked for desired cosmetic effects. -> Things like lookups are easy to generalize, since they all come out of

   the relations.

With a setup like this you will have a lot of people howling that you have broken all kinds of rules, but you will have tremendous power and flexibility.

For instance, many developers live by the rule that you must know everything in advance before starting a project. I'd love to work in such a situation, but it has never happened to me yet and I've come to grips with the fact that it probably never will. Therefore, I need something that is self-documenting that will allow me to rapidly change the schema and the code that is used to suppport it. Systems such as this have always done that for me.

-- 
Kenneth Downs
Received on Wed May 14 2003 - 04:18:41 CEST

Original text of this message