If a co-worker were to ask you to name as many Oracle table
types as you can, what would be in your list? More than likely you would start
off with relational, object, "normal," cluster, index-organized,
nested, partitioned, and so on. "No, not those table types," your
friend says, "I mean these types: stage, history, archive, operational,
fact, dimension, and so on." The correct answer in this scenario depends
on the context or frame or reference being used. In designing a database, it is
easy to focus on the structure of tables and to lose sight of how tables are
going to be used. Put another way, what is the purpose or function of a table?
Being able to classify a table helps in two common
situations: at design time when designing a schema/database, and in cases where
you inherit someone else’s work, that is, when you need to understand what is
already in place. You may already have an intuitive understanding of how tables
are used, but the ability to specifically identify a table’s purpose or
function is useful in that it allows you to communicate requirements to others,
whether they are database administrators or database developers. Let’s start
off by considering a data workflow consisting of in, out, and record.
Stage tables
For many applications with a database back end, stage tables
are the first point of entry (or first line of defense, depending on your
perspective) for data entering a database. In their simplest form, stage tables
are images of the contents of a data file. In terms of purpose, stage tables
serve as intake, upload, or collector mechanisms. A very common method of
populating stage tables is by using SQL*Loader. Stage table loading can also be
used to perform data hygiene. Create a unique index on a particular column, and
the upload process takes care of removing duplicates. Other bad or discarded
line items can be collected and reported back to the data provider. Depending
on your needs, you can also use stage tables to begin or perform transformation
processes.
From a use perspective, you will probably want to reuse stage
tables. The application or database – it doesn’t matter which one, just as long
as it gets done – should perform a clean-up or preparation step prior to
uploading data. Is it done by using REPLACE in SQL*Loader or by a prepared
statement in Java which uses TRUNCATE TABLE? From a design perspective, does
the table need to have logging enabled? If so, why? Are you ever going to
rollback or recover anything in a stage table? Probably not, so keep the DDL
type of features or options to a minimum. Now that the data is inside that
database, where does it go next?
Operational tables
One way to define tables of this type is to consider what
they contain. Live data, such as customer or account information, can be stored
in operational tables. These tables are the guts of the database, so to speak.
In one form or another, everything in an application revolves around the
contents of these tables. Operational tables should be first and foremost with
respect to security, backup, and recovery, and therefore, should employ whatever
means or features to meet these requirements. This includes logging, enabled
row movement for flashback, explicit permissions, auditing, fine grained
access, virtual private database, and whatever else your situation/application
may require.
These tables are further characterized by how they are
designed. Normalization is a word or process that should immediately come to
mind, and along with this comes the consideration of related objects such as
indexes, views, sequences, and triggers, to name a few. These tables are what I
referred to as "normal" tables in the introduction and normal seems
to be a good fit for two reasons. First is that these tables are normally seen
throughout a schema, and second is that these tables typically need to be normalized.
Another type of operational table is one related to how an
application or schema operates. An example would be storing information about
disposition tables (see below). Another example is familiar for those who
design security (roles and permissions) into a schema as opposed to having
Oracle manage this function.
Functional tables
Lookup or intersection tables fall into this category as
their main purpose is to perform a function (i.e., resolve a many-to-many
relationship). Another type of functional table is a shopping cart/order entry
table (contains an order number and one or more line items). These tables are
relatively easy to identify and their purpose is discussed in virtually all
database design books. Functional tables bear mentioning because of their close
association with operational tables; however, not all tables are created equal.
Identify the crucial functional tables and place them into
the same category with respect to the importance placed on operational tables.
How can you distinguish between them? One way is to consider how the contents
change, that is, is the data static or not? Static tables (e.g., job code
versus job description) can be repopulated from a script. Dynamic tables
require recovery. Do you directly populate these tables or is it done
indirectly (via a trigger)?
Disposition tables
Tables of this type are frequently created by performing a
join between a stage table and a combination of one or more operational and
functional tables. Disposition tables are commonly used for reporting or
staging data to be spooled out into disposition files. You may find many tables
of this type in a schema, all with the same definition. What differentiates
them is the date they were created or the number or records (weekly report or
newsletter recipient list consisting of fewer rows as compared to an end of
month report or recipient list).
A disposition table can also be similar to a stage table in
that its contents are routinely flushed and repopulated for a pending disposition
or report process. An alternative means of keeping disposition data is to
borrow from the data warehouse side of things and use a flight history type of
table. You will not need to retain 100% of the disposition table’s contents,
but you will need to capture what is essential. Who sent what, and when, and
can details be reconstructed if necessary? If details are lost due to data
changes elsewhere in the database, then the record keeping requirements become
more complicated. It then becomes a trade off between keeping scores of
disposition tables and maintaining a huge flight history type of table.
Archive tables
A close cousin on the production side to a warehouse’s
flight history table is the archive table. By its nature, an archive table’s content
is permanent, so one way to delineate the life span of what is in a disposition
table is to consider the degree or permanency of the data. Forever (or, at
least whatever the service level agreement retention policy is) equals archive,
and something more short term is subject to being disposed of in a disposition
table.
Other tables
Audit tables are discussed in detail throughout much of
Oracle’s documentation and generally so in most textbooks. It is not the data,
but who did what to the data and when. One type of table being seen more in
applications is the metadata table (data about data). Not sure of what an
example of this type of table looks like? Several tables in Oracle’s data
dictionary are excellent examples. The DBA/ALL/USER_TABLE family contains data
about a table’s data (number of rows, last analyzed, etc.).
In Closing
Production database tables, for the most part, seem to lack
the same naming clarity as seen in data warehouses. Fact and dimension tables
have clearly defined names because of their purpose, but what type of table is
your customer account table? Whether or not you agree that the type name should
be operational, what does matter is that everyone involved in the management of
this type of table have the same understanding as to its purpose. If someone
asks you to name table types in Oracle (and this applies to other database
systems), ask that person if he or she meant physical or logical, how they are
designed (DDL) or how they are used, production or warehouse, and so on. You
can help expand that person’s frame of reference by going beyond what is in the
Database Concepts documentation.
