Re: [libreoffice-users] Adding fields to existing Base table

Hi Dave,

Having worked as a business systems designer and coder, I've dealt with
databases and database changes a lot. And the users always end up
changing stuff later. Some of what I am going to say may be obvious or
stuff you already know; if so I apologise, but your question makes it
seem like you are unfamiliar with databases, so I will try to cover the
basics.

The short answer is that this sort of thing is done all the time,
applies to all databases, and is pretty easy stuff, if somewhat
timeconsuming for the client, unless there are complicating factors.

At least, pretty easy just from the database side of things, I'm not
sure if LO makes it harder than it should be, as I haven't used LO Base
much. I'm mainly talking about the underlying principles here, which
your post suggested to me that you don't fully understand yet (again,
sorry if I got that wrong).

The long answer...

First off, if your database may grow to 3 Gb, it suggests that this may
be a serious application. A rough guesstimate suggests maybe a million
rows, and that sort of growth in a year means about 8 rows a minute
added (very rough guesses, but just to get an idea of the scale we
are talking about). I would think carefully now, rather than investigate
at some later date. As often happens, you could end up with the client
deciding that it's not worth the cost of making a change, and whatever
poor choices you made up front "just for now" end up sticking with you
and being a major headache when things get beyond their capabilities.

Straight away I question using LO at all. A custom front-end often has
many advantages in this sort of situation, making them worth the cost.
But that's the sort of thing I used to do for a living, so I guess
that's my instinctive response, not to say LO isn't a good fit for
this; I don't know the requirements, so I can't say.

But at the very least, I would *strongly* suggest using an external
database, not the built-in HSQLDB. ***With a good backup solution***.
And consider where it will be hosted. If the database is that big, you
need to consider questions like how many people will be using it at the
same time, and how many transactions will be occuring per second. The
hardware used for the database server makes a big difference to the
responsiveness of these sorts of systems.

As to adding fields, you just got to live with that one. Happens all
the time. No amount of getting the client to think carefully about his
design is going to prevent them coming later and wanting stuff changed.
So says my experience.

Luckily it's not hard to do, per se. The problem is of course that if
you add fields, you are adding them to the structure of the table,
which determines what will be in each row, so it affects not only rows
that will be added but also all already existing rows. And therefore
each existing row needs to have something (even if the something is
nothing) in that new field. That will always be a problem. Luckily,
mostly a problem for the client, who now has to go back and add all that
missing info for the existing rows, to make sure that the data in the
database makes sense.

So when you say that the docs said it might be painful, that's not so
much from the practical point of actually adding the fields, but from
the point of making the existing rows make sense with the new
information. So yes, it applies for all databases. They all make
actually adding the new field pretty much just as easy, but the
reconciling the existing rows has nothing to do with the database and
everything to do with the data itself.

When adding new fields, as with adding fields at the beginning when
designing the table, you can choose both a default value for a field,
and to allow the field to be null. Nulls are misused and abused a lot,
and different people have different ideas about how to use them. It's a
whole other topic to explain how to use nulls properly, but suffice to
say that when you add a field to a table, you can allow the field to be
null, and then all existing rows in the table will simply have null for
that field. Or, you could choose a default value for the field, and
then all existing rows in the table will have the default value for
that new field. That value could be a blank value, like an empty
string, which is different to a null value.

So for simple additions of information, the developer's job is pretty
straightforward. Yes, you have to add that field to the table, and then
to all forms and reports, but it is the client who has to make sure all
the new and existing entries have valid information in that new field.
The problem for you as a developer comes in when they want additional
functionality tied in to that field. Given that you're developing this
in LO, I'm guessing that there won't be too much of this, it will
mostly be simple informational fields. But some of the stuff I've done
in the past has required things like "when they fill in this field,
they must get this custom screen to choose a value from, and then, if
they choose "A", they must get this custom icon on the report, and if
they choose "B", then this mustn't show up on the report at all, and
instead must make one of the other rows show up differently". Ok, not
actually anything like that, but you get the picture. That's some
serious developer headache there.

As to deleting fields, it's also pretty easy. You just drop the field,
and the field is gone from all existing rows. Which means loss of some
data. Which presumably is what you want. Provided, of course, there are
no other considerations, like other tables linking to this table via
the field. Then it gets a little trickier, and really depends on what
you want to do.

Sorry if this post was rather long, but I hope it showed you the basic
factors you need to consider with the database design.

Paul



On Thu, 10 Jul 2014 15:27:27 -0400
dave boland <dboland9@fastmail.fm> wrote:

> I'm setting up a database that is small (three tables, may grow to 3GB
> over next year).  I need a strategy to deal with the unknown, which is
> how to add fields to an existing table.  I read in the docs that doing
> this can be painful and it is required to put something in each field
> for each record.  Do I have this correct?  If so, how do I handle the
> inevitable "...would you add..." that is sure to come within the next
> few months?  I would add them now, but I really can't anticipate how
> many fields will be added or their requirements.
>
> In general, do other databases have similar restrictions?  At some
> time, when I have time, I will consider MySQL, MariaDB, and others.
>
> Thanks,
> Dave


-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted