As soon as someone says it is/should be "generic" then you should stop take a deep breadth and if possible rejected it authoritatively or escape from that mental hospital.

(speaking based on experience)

Tom,

Thank you for that excellent article.

However, it won't really help me deal with this problem unless I provide specific examples/etc, which I'm not sure how to generate for this specific issue.

Do you have any ideas/examples that would help?

Can you think of any approach where generic would be the right one?
Let's say your bussiness is handling documents from different clients (physical paper ones or digitized ones). There are lots of different documents and different specs about which information would be stored depending on the type of document for every client. Wouldn't be a generic DOCUMENT table a right approach? I know the design would have more tables, of course, but with one like this as the main table to store the information.

What's your opinion?

An XMLType column is very flexible - lets users store whatever new data they can think of, without having to restructure the database all the time - and without having to get DBA's involved whenever some new piece of data needs stored. Of course, the fun begins when you want to query it - but then Oracle has XPath and/or XQuery for that.

This is a never ending debate. There's no getting away from the fact that there are *business* requirements for users to be able to specify what data they want to store.

Whether that is then implemented using XML, or plain SQL "generic data models" (which run like a dog), or by an application issuing DDL statements all the time to add new columns (try getting that past a DBA!), does not change the fact that it is business users who are asking for this functionality.

Purportedly the Kalido ETL tool uses a generic "thing" table to store both data and metadata.
</code> http://utopia.csis.pace.edu/dps/2007/gghare/references/Kalido/1kalido_dwlm_white_paper.pdf <code>
"From a technical point of view, it may appear that generic data storage will impair the performance of the data warehouse. Empirical evidence suggests otherwise: commercially-available relational databases have been shown to perform as well, or very nearly as well, with generic designs as with traditional designs. Where performance is an absolute priority, a hybrid design may be considered, in which the performance-sensitive portions of the data warehouse are handled conventionally, while the rest of the data is handled generically."

It would be interesting to see a TPC benchmark on generic ("configured") vs custom ("tailored")!
</code> http://www.tpc.org/tpch/default.asp <code>
I like the quote from your cited URL:
"early Computer-Aided Software Engineering (CASE) ... included a table named SDD_ELEMENTS that contained both data and metadata indistinguishably... [BUT] what works very well for a CASE application supporting 1–5 developers who think a lot and type only a little may not work well at all for a mission-critical order-entry application supporting 300–400 reservation agents who think very little and type very rapidly. The database workload generated by a small number of users generating transactions intermittently has little in common with the database workload generated by an order-entry application."

Vega asks "Can you think of any approach where generic would be the right one?"

This object/object-attribute structure seems like it would be useful when used by an Oracle text user-datastore to construct a logical xml document for text queries.

Provided it is only used for that purpose and only Text queries, I think it's use is valid.

Tom, what do you think?

I was thinking that the db would want some control over what shape the document would have and which attributes were included without incurring the cost of XSLT.

I was referring to what I expected to be the greater processing cost of XLST.

But, if the database can perform an xslt transformation of an xml clob more quickly than one could construct the same from an attribute table, great.

yes, xml and xmltype offers many options.

but in this case (the generic table thingy):

- "you have the ability to function base index the xml"
yes, function based indexes are possible (but when using extractvalue() and friends, does Oracle parse xml once/insert-or-update or once for each index?)

- "you can leave it as a clob"
shedded xml is not possible because an xml schema can not be registered in this context,

- "you have to use a clob"
you have to,

- "you can text index ALL attributes for \"fast\" retrieval"
yes, if that means using a CTXXPATH index; but, doesn't that index the tag names and not the values,

- "you can text index SOME IMPORTANT attributes for "fast" retrieval by them"
not really possible without an xml schema.

as far as my comment to xslt goes, I was assuming that different structures of objects were being serialized and it's desired to have some sort of consistent query.

I was only pointing out that Oracle Text (on it's own) can be used as a powerful query tool and the o/oa structure can be used to construct something to query. I think storing XML and querying with xpath or xquery is often overrated or overused. I like the Oracle text-over-xml facility.

Btw, when is Oracle going to allow limited numeric queries in Text. That'd be very powerful and pretty doable I think.
ie something like:
contains(doc, 'yadda yadda and (expr(gt, 20061001) within thedate)') > 0

- "indexes are maintained only during the modification, so the extractxml is performed once on the insert and not during the retrieval."
yes, but if I have two function-based indexes (using extractvalue or friend), will the the clob be xml-parsed once or twice? I hope once. XML parsing is pretty heavy.

The consistent query model business was just wanting to normalize what made sense. So that, say, searching in a title would hit both newspaper articles and books (which may have been serialized differently).

[Tom, fwiw, I *really* appreciate this dialog. (Who else does that?) It's pretty interesting and has caused be to explore corners of Oracle I had not considered.]

<quote>(Who else does that?) It's pretty interesting and has caused be to explore corners of Oracle I had not considered.]</quote>

It had started with [relational] data models, went through generic models and the utopia of Kalido's marketing machine only to get swamped in a sea of acronyms all starting with 'X'. How better can it get?

One of my pet peeves is misusing the term "normalization" to refer to abstraction or generalization. Thank you for pointing out the difference! They are orthogonal / independent concepts!

Normalization <--> denormalization
Abstraction <--> concretization (it's a real word!)

The famous SDD_Element table in Oracle CASE is a generic model... and it's well suited to its target application.

As the Bad CaRMa article points out, it would not be good for a transactional system.

Oracle Applications Flexfields are another less generic but still quite generic solution that work well in transactional business systems. Huh, fancy that.

The Oracle DBMS itself has an extremely generic data model tucked away in its internals. Surprise surprise.

Most serious applications have at least some generic functionality mixed in - you need some kind of generic fields or flexibilty to allow minor changes in data model and business practices without calling in the programmers.

The key is to balance the cost of generic designs with their benefits, to use them where they are most valuable, and to make them as cheaply generic as possible given the business requirements.

I am referring to the internal tables that views like ALL_TABLES, etc. are based on.

Look, for example, at sys.obj$.

To give people an understanding of how many different distinct database objects are stored in sys.obj$, here's an excerpt from the definition of the view ALL_OBJECTS:

decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, NVL((SELECT distinct 'REWRITE EQUIVALENCE'
FROM sum$ s
WHERE s.obj#=o.obj#
and bitand(s.xpflags, 8388608) = 8388608),
'MATERIALIZED VIEW'),
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
60, 'CAPTURE', 61, 'APPLY',
62, 'EVALUATION CONTEXT',
66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
72, 'WINDOW GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
81, 'FILE GROUP',
'UNDEFINED'),

It would be nice if Oracle had a published white paper on the pros and cons of generic models, and what kinds of performance problems can come out of generic modeling.

John

Watch out Tom, a new competitor is in town: http://www.amazon.com/b?ie=UTF8&node=342335011

Some quotes:

"Amazon SimpleDB differs from tables of traditional databases in several important ways. First, you have the flexibility to easily go back later on and add new attributes that only apply to certain items - for example, sleeve length for dress shirts. Additionally there is no need to pre-define data types. If you have an attribute called "size," it can have values of 10.5 for shoes and XL for sweaters, making the service extremely flexible and easy to use."

"Amazon SimpleDB automatically indexes all of your data, enabling you to easily query for an item based on attributes and their values. In the above example, you could submit a query for items where (color = blue INTERSECTION description = dress shirt), and Amazon SimpleDB would quickly return item 456 as the result."

"Amazon SimpleDB requires no schema, automatically indexes your data and provides a simple API for storage and access. This eliminates the administrative burden of data modeling, index maintenance, and performance tuning. Developers gain access to this functionality within Amazon's proven computing environment, are able to scale instantly, and pay only for what they use."

I suppose it could work for some, well, very simple databases (ahh, I get the name now!)...The funny part is there's kind of a buzz about this new database being revolutionary, and I've even read comments that Oracle and MySQL should be worried...

Hello Tom,

I am designing a dimensional database model for a scientific research org.. I have a fact table item_fact in which we are storing item_id and different physical characteristics of item. Since physical characteristics are changing all the time, we can not have fixed columns in this fact table to stores them. As I am fully aware of drawbacks of generic model(Thanks to YOU)I have decided to go for all possible column + some flexi columns in the fact table. Now I can design fact table in two ways:

a) Give all column a meaning full name e.g. item_id, color_after_boil, color_before_boil....
But then I am ending up with 104 column in fact table.

b) If I do not use proper column name for example color_after_boiling and use col1 for it then I can store values in 19 columns of different data types. In this case my fact table will be item_id, col1,col2,col3......col20 but I will loose the meaningful column names

My questions are:

1)Between a and b, Which one is a better approach? Is there any way by which I can avoid loosing meaningful column name?

2) Is 104 column fact table is a good idea? What will be the impact of it on storage and full table scan?

We are using OBI for reporting purpose.

Thanks,

ARVIND

Sorry for asking basic level question but how can I add metadata?

Thanks for your quick response.

Regards,

ARVIND

THANKS - A - LOT----> to YOU.

(YOU == GREATEST PERSONS. You is for others) :-)

Regards,

Arvind

From experience, a "single table that does everything" is about as useful as a java/c/c++ "one-liner".

For Arvin, my experience with writing scientific applications:
A view/materialized view like
item_id, test_name_a, test_name_b, color_before_boil, ...
is better for the non-SQL savvy end-user who is accessing the data through a read-only ODBC connection (eg Excel, SAS, other statistical software packages). This is usually used for the warehoused/published data.

2nd) due to our business requirements, this design sucks for the application that collects, analyzes, runs through 'approval' process, and 'publishes' the data.
-- reason 1) each c1, c2, c3, ... requires ~14 columns
-- reason 2) on rare occasions, we would have to "DEL COLUMN" in this design
-- reason 3) i'm not lucky enough that c1 has one and only one result value per test run. (which increases column count)
-- reason 4) it appears that i'd be trying to make a single table that does everything (a la Vision)
-- ** i do use this design for consolidating and reporting data.

3rd) (for John also) (NOTE: i am not an expert)
From experience, if and only if the table has a very well defined/specific reason in life, then a table like:

create table results_for_physical_properties (
rfpp_data_index Integer primary key,
some_foreign_key integer,
item_id integer,
physical_test_id integer, -- some tables use varchar2
result_value float -- some tables have result_x, result_y
-- other tables need (result_value varchar2)
);

works great, especially when (item_id, physical_test_id) is known to be non-unique. (ie item_id=x was tested multiple times for test_id=y). For me, what makes this table specific is that I have another set of tables that contains a table called "results_for_chemical_properties".

If i'm understanding correctly, what would be BAD would be a design like:
create table any_result (
result_type int, -- 0== physical, 1==chemical, ...
item_id int,
test_id int,
{some set of columns who's meaning changes based on "result_type"} -- this is the part that makes it bad.
);

I would love to hear everyone's comments. Especially Tom's.

Thanks,

Mike Kutz

I have a developer who would like to create a generic table with, say, 10 columns each of string, number, date datatypes and a "type" and a sequence generated PK. There would be an index on type+pk, that would the only access path.

He would then create specific views on the table depending on specific uses of it.

create view cust as 
select
 id cust_id,
 cast(c1 as varchar2(30)) cust_name,
 cast(c2 as date)         dob,
 cast(c3 as number(10,2)) some_numeric_attribute,
 ...
from generic_table
where type='cust'

Your thoughts? Thanks

This is another example of the old dilema between the normalization versus the de-normalization forces at design time. Each application has it is own sweet point in the saddle. But is never the extremes.

One table = full denormalization, this is extreme.

Too many tables = full normalization, this is the other extreme.

How can a developer be given a chance to implement any of the extremes?

A fundamental principle in the design of any tool used by human kind is not necessarily the full stress of the extremes if optimal performance is in the goals.

Business users will yell and scream that they need the ability to
define attributes about data, especially when that is how the current
application works. (ie, you take it over and they already have these
name/value pairs). Okay, so what really happened? How come these
things exist? Its because we, the developers can't react fast enough
to support all the business's needs RIGHT NOW!!!!, nor should we react
that fast. A design change should mean something. Like in Excel, they
think that they want to add a new column to a worksheet and start
maintaining it on the rows in that sheet so they can then filter on
that column for their daily work. We need to be able to give them
that new column, basically, right when they ask for it. But, we
can't. We need requirements gathering, design, testing, and then
deployment. So, can we bridge the gap? I say, okay, go ahead, let
them add that new name/value pairing. Then, start putting things in
place which monitor these things when they show up. Triage them as
they do and as they grow. Then, when they actually truly are needed,
modify the datamodel and the dependent applications to support this
new attribute. Update that attribute from the settings found in the
name/value pairing. Then, add yet another constraint on the
name/value pairing that does not allow that type in anymore.

Basically, allow that to be the pre-cursor to requirement gathering.
When reports are needed with that information as part of the report,
those name/value pairings shouldn't be generic anymore. They should
have made their way into the model.

On the other hand though, the users should have to answer for
name/value pairings that aren't useful. (ie, the monitoring should
also notice if a NVP actually has many values and such). If no, you
should have a purge agreement as well.