DB Design: Extending the DB schema over time

#11

On 8/10/2011 10:16 AM, Ulrich Scholz wrote:

Quote:

Thanks for giving me inside into the topic. And sorry that I did not
look into
database normalization immediately, as you suggested. It was very
helpful.

On 25 Jul., 12:25, Jerry Stuckle<jstuck... (AT) attglobal (DOT) net> wrote:
Properly designed, a database should almost never need to have its
structure modified. The fact you're even thinking of having to do it in
the future indicates a potential problem here.

I see your point and that's probably true for many data bases. But, I
guess,
not for all:

The application that I design collects data from a productive system
on a
regular schedule and over a longer time. This data is processed to
infer
further data and finally to issue notifications if certain conditions
are met.
These conditions include information about earlier notifications, so
knowledge about them has to be recorded, too.

This system will evolve in two directions: On the one hand, the
productive
system will change and the collected data will change accordingly. I
expect
these changes to be incremental. On the other hand, our knowledge
about
the data will deepen and we will come up with new data to be inferred
and
new notifications to issue.

What is your suggestion for a database of this kind?

UIrich

It's very difficult to say without a lot more information. Not that I
didn't say a database design should *never* be changed - but properly
designed, it should *seldom* be changed.

But for instance, you could have something like:

table test
-------
test_id
description
other data as necessary

table test_item
-------
item_id
description
other information as necessary

table test_run
-------
run_id
test_id (refers to test)
test_date_time
other information as necessary

table test_item_result
-------
run_id (refers to test_run)
item_id (refers to test_item)
result

The test table describes a basic test and has generic information about
the test. Adding more tests just means adding additional rows to this
table.

test_item describes a particular item being tested, and again has
generic information about what's being tested.

test_run is a particular run of a specific test. It contains
information specific to a particular run of a test, such as the
data/time the test was run, any particular conditions under which the
test was run (i.e. the mouse was drunk when he ran the maze), etc.

test_item_result contains the result for one item of a particular test.
If the test has multiple results, you will have multiple rows in this
table. Note that if results are of different types (i.e. some numeric,
some character, some time duration, etc.) you may have multiple result
columns in this table, holding different types of data. In that case
you may want a column in test_item to identify the result type for
easier programming.

In this way you can add as many tests as you want, each test can have as
many results as you want, and you can run them any number of times, all
without changing the structure of your database. It uses one-to-many
relationship in normalizing the data.

A pretty simple example, but with as sketchy as your information is,
it's hard to come up with anything more concrete. I hope you get the idea.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

DB Design: Extending the DB schema over time

comp.databases.mysql comp.databases.mysql

Re: DB Design: Extending the DB schema over time - 08-10-2011 , 12:02 PM