Database Architecture and Design

Modelling Tools

Under Construction

Architecture

The architectural design of a database defines where the database fits in the scheme of an entire (software) system. If you want an analogy - think of the analogy where an Enterprise Resource Planning (ERP) System is a city and and the database represents several City blocks within that city each with its own architecture - based on a common style (hopefully -- and assuming the design was coordinated [Note 1]). Each city block would represent a "module" such as General Ledger, Accounts Payable, Distribution or Sales management - all complete systems in their own right and possibly functional in a stand alone mode. From any building within any city block you might have restricted views (or sweeping views from the corner suite) of the the other city blocks or buildings (data for other program suites). The traffic (data) moves through the streets - predefined pathways of a given size and structure... But enough of the analogies - the point is that a database has a size, scope, traffic flow systems to avoid grid-lock and many other large scale characteristics that gives one a sense of form and style that define the whole. The whole is greater than the sum of the parts -- the relationships among the many structures add to the character and function of the system.

What is a Database Architecture?

To paraphrase a definition of software architecture [3BENNETT] perhaps we could say:

A relational database architecture specifies data sets and a set of data relationships such that the information contained therein will have meaning with a given context. The relationships are based on set theory and form a system of predicate logic. These relationships are necessarily independent of the material realization of the system in hardware (Computer Hardware and Operating System Choice) and software (RDBMS choice). Furthermore the architectural specification dictates the size (or scale) of the system such that material dependencies can be resolved when material choices must be made at the end of the logical design phase.

Databases and Set Theory

The concept of a Relational Database is derived from set theory. So if we are to talk about a Relational Data Base or a Relational Database Manager (RDBMS), then the underlying database must work in a fashion consistent with the laws of set manipulation. If you want to talk about non-relational databases - then perhaps you should be talking about a list manager, or a data file manager. After all, if there are no relationships between the data sets - then why why bother even to amalgamate them? All RDBMS's that hold the same data, and have been programmed with the same referential integrity rules must give the same answers. Reality is different. One RDBMS will often give a different answer than another RDBMS. If you do not understand Relational theory (and set theory), then you will not know the difference between a bug, a programming error, data entry error or a design error. A Relational Database designed by a knowledgeable person educated in Relational set theory will be more reliable (consistent) and typically "faster" than a database that has been "hacked".

The Concept of a Set

Let us use the following (paraphrased) informal definition of a set [2STOLL]:

A set S is any collection of definite, distinguishable objects - real or imagined - to be conceived as a whole. The objects are called the elements or members of the set.

Note the word distinguishable - it is of the first order of importance. For those of you who know the meaning of the word null, or the phrase primary key it should strike an immediate chord.

For more information of Set theory go here.

In The Beginning...

So what comes first? The (application) system architecture? -- or the data system architecture? That depends -- did someone say "Gee we have all this data - we need to store it -- I just know we have something useful that we can use some day." -- or did they say "We want to open a business selling widgets and we need to track not only the widgets but all the steps in the manufacturing process and who handled it and the materials used and the assembly methods and.. and..... so on". In the first case there should already be a good understanding of the data relationships - since we are discussing an already established system. In the second you might have to define the data, the processes the stake holders etc. The latter is clearly an Application Architecture job and probably a Systems Architecture job as well. The first situation looks like a "Data Warehouse" application.

Under Construction

The following sections will be added as time permits.

!

Notes

[Note 1] Anyone who has committed to build an interface to a large ERP and then subsequently analyzed the database of a large ERP has probably had a sinking feeling in their gut that they bit off more than they could chew. Often times the database design looks like that of of high school kids run amok - not like that of a company with tens of millions in their budget. This might be excusable in a small company driven by tight deadlines and forced to adopt a "good enough for now" philosophy - but inexcusable in a major vendor with products 10 to 20 years in development.

References:

2STOLL] Stoll, Robert, Page 2 Set theory and Logic

[3BENNETT] Bennett, Douglas, Page 2 Designing Hard Software, The Essential Tasks

Found a mistake? A broken link? Let the Webmaster know. Your assistance is always appreciated!.
Material Copyright © 2004 PMC

Updated January 31, 2005