DB Design: Extending the DB schema over time

#1

Dear all, I'm about to design a MySQL data base. I have some basic
knowledge about relational databases but am no expert. In particular,
it's my first time as designer.

The data, the DB is about to hold, is hierarchically organized. In the
end, it's a bunch of XML files. Once in a while the XML structure will
be extendend incrementally, i.e., new elements and parameters might be
present. My concept is that the DB resembles the structure of the XML
files.

Now my questions:

1) Is it esaily possible to extend DB tables with new attributes
(columns) in case the XML structure is extended? Note that I will
extend the DB by hand, no automatic detection is required.

2) How can I prepare my application for such changes in the DB? In
particular, existing SQL queries should stay correct and yield the
same results as before.

Thanks, Ulrich

#2

Ulrich Scholz:

Quote:

1) Is it esaily possible to extend DB tables with new attributes
(columns) in case the XML structure is extended? Note that I will
extend the DB by hand, no automatic detection is required.

That's an easy question: with a proper tool (like PHPmyAdmin), such
changes are easily made.

Quote:

2) How can I prepare my application for such changes in the DB? In
particular, existing SQL queries should stay correct and yield the
same results as before.

May I assume you just meant SELECT queries?
In that case "it depends":

a) SELECT * FROM someTable
will yield different results when you add a column. But isn't that the
whole purpose of adding the column?

b) SELECT a,b,c FROM someTable
will yield the same results when you add a column. For this query
alone, adding a column would be useless.

[to other denizens: these examples were not intended to suggest how to
write select queries, but merely short examples]

--
Erick

#3

On 7/22/2011 6:16 AM, Ulrich Scholz wrote:

Quote:

Dear all, I'm about to design a MySQL data base. I have some basic
knowledge about relational databases but am no expert. In particular,
it's my first time as designer.

The data, the DB is about to hold, is hierarchically organized. In the
end, it's a bunch of XML files. Once in a while the XML structure will
be extendend incrementally, i.e., new elements and parameters might be
present. My concept is that the DB resembles the structure of the XML
files.

Now my questions:

1) Is it esaily possible to extend DB tables with new attributes
(columns) in case the XML structure is extended? Note that I will
extend the DB by hand, no automatic detection is required.

2) How can I prepare my application for such changes in the DB? In
particular, existing SQL queries should stay correct and yield the
same results as before.

Thanks, Ulrich

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.

For instance, elements and parameters should probably not be columns in
a table; they should be different tables, related back to the original
table.

Read up on database normalization for more info.

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

#4

Ulrich Scholz wrote:

Quote:

Dear all, I'm about to design a MySQL data base. I have some basic
knowledge about relational databases but am no expert. In particular,
it's my first time as designer.

The data, the DB is about to hold, is hierarchically organized. In the
end, it's a bunch of XML files. Once in a while the XML structure will
be extendend incrementally, i.e., new elements and parameters might be
present. My concept is that the DB resembles the structure of the XML
files.

Now my questions:

1) Is it esaily possible to extend DB tables with new attributes
(columns) in case the XML structure is extended? Note that I will
extend the DB by hand, no automatic detection is required.

Yes, with reservations. Applications that expect a certain structure may
need altering.

Quote:

2) How can I prepare my application for such changes in the DB? In
particular, existing SQL queries should stay correct and yield the
same results as before.

The golden rule is not to change field names, or field relations.

select * may also yield different numbers of fields, which can impact
some post query manipulations..

Ultimately its surprising how easy it is to add fields, and how easy to
get things to work, and how difficult it is to change relations.

The key is to be disciplined and maintain a written documented 'data
dictionary' that defines the tables, the fields and the relations
between them. And also similar for e applications, so that you can read
the specs of the apps, and identify what impact a relationship change
may have.

E.g. whilst is easy to - say - add an extra field like ''hair colour' to
a database of people, if you want to move the field 'employed by' from a
simple field to a one to many relationship of companies to people, its a
huge change throughout the whole application suite.

> Thanks, Ulrich

#5

On 22/07/11 12:16, Ulrich Scholz wrote:

Quote:

Dear all, I'm about to design a MySQL data base. I have some basic
knowledge about relational databases but am no expert. In particular,
it's my first time as designer.

The data, the DB is about to hold, is hierarchically organized. In the
end, it's a bunch of XML files. Once in a while the XML structure will
be extendend incrementally, i.e., new elements and parameters might be
present. My concept is that the DB resembles the structure of the XML
files.

Now my questions:

1) Is it esaily possible to extend DB tables with new attributes
(columns) in case the XML structure is extended? Note that I will
extend the DB by hand, no automatic detection is required.

2) How can I prepare my application for such changes in the DB? In
particular, existing SQL queries should stay correct and yield the
same results as before.

(shamelessly plugging my own howto)
You may find this helpful:
http://www.howtoforge.org/node/4833

Good luck,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

#6

Quote:

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.

For instance, elements and parameters should probably not be columns in
a table; they should be different tables, related back to the original
table.

Could you point me to some instrucional example?

Thanks, Ulrich

#7

On 7/25/2011 3:35 AM, Ulrich Scholz wrote:

Quote:

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.

For instance, elements and parameters should probably not be columns in
a table; they should be different tables, related back to the original
table.

Could you point me to some instrucional example?

Thanks, Ulrich

As I said (and you clipped) - read up on database normalization.

http://www.lmgtfy.com/?q=database+normalization

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

#8

Quote:

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.

So, it's bad practice to design a database to do sales order
processing for a lemonade stand NOW, looking ahead to have it also
handle inventory and multiple retail outlets next year, payroll a
couple of years down the road, and deal with the issues of being a
multinational corporation (multiple currencies, many more tax
filings, etc.)a decade down the road if that happens?

The kid can't afford software for a multinational corporation now.

#9

On 7/25/2011 11:02 AM, Gordon Burditt wrote:

Quote:

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.

So, it's bad practice to design a database to do sales order
processing for a lemonade stand NOW, looking ahead to have it also
handle inventory and multiple retail outlets next year, payroll a
couple of years down the road, and deal with the issues of being a
multinational corporation (multiple currencies, many more tax
filings, etc.)a decade down the road if that happens?

The kid can't afford software for a multinational corporation now.

Yup. A database handling inventory, multiple retail outlets, payroll,
multinational issues and the like is far different than one running a
lemonade stand. The database should be completely redesigned.

And BTW - it will probably be a real database (i.e. SQL Server, Oracle
or DB2) running on a mainframe, not MySQL on a PC.

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

#10

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:

Quote:

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