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

Just a few thoughts...

On Tue, 15 Jul 2014 19:30:47 -0400
elderdanlewis <elderdanlewis@gmail.com> wrote:

>    If you are going to create a database, you plan it THOROUGHLY!
A nice theory, but in my experience working in the field this is almost
never completely possible. Often one will get a fairly detailed spec to
start with, sure, and that you will plan thoroughly, but you just know
that it will change at least a little (probably significantly) during
the development lifecycle, and almost certainly have some additions
afterwards, if not outright changes.


> Beginning with a small simple database and expanding it to a very
> large one is only asking for a complete disaster!
Well, that depends, in my opinion. Starting with a simple database when
you aren't sure of the requirements, and redoing it (maybe more
than once) when you have a better idea of what you need sounds like a
great way to go. Just don't expect to simply expand the simple
database, you probably want to replan it and re-create it. You may be
able to port the data across, if the changes aren't too big. If the
changes end up being really rather small, you can just alter a few
tables, maybe add one or two, but that gets messy quickly.


> Unless you are using a flat database, your tables need to be in at
> least fourth normal form.
Well, I dunno, it's been so long since I bothered with normal forms
(only at college, really, hardly ever in the workplace), that I can't
rightly remember what the standard was. Sometimes one normalised,
sometimes one had to denormalise, but I can't even remember when that
was. Mostly, in the workplace, we just used what made sense. What made
sense was really a feeling one got from experience, not from any set of
rules one learnt during college, although they probably just became so
ingrained one didn't think of them anymore. And the "at least" reminds
me that we were taught that you didn't want data that was *too*
normalised, as that lead to its own set of problems (not that I can
remember what those were...).


> Repeatedly adding additional fields can cause the table containing
> these fields to loose its normal form. Then there is no way to know
> if your quieries and reports contain acurate information or not. The
> purpose of a relational darabase is to enter data without any
> repetition of it.
Not actually so serious, in practice, at least not on a small scale. In
theory a bad thing, and I guess the idea when one got the chance to
design from scratch was to avoid that, but after a while things always
seemed to hang together by prayers and bubblegum. Some of the old
projects I worked on...

The clients either didn't want to pay for a rewrite, and made do even
when they couldn't actually get quite the data they wanted, only
something close, or they actually found that despite everything it
worked well enough for them in practice...

I guess sometimes if it works for the most part, it's good enough.
There was a part of me that hated making yet another kludge to a system
that was already a woodpecker away from collapse, though.


> http://wiki.documentation.org/Documentation/Publications has chapter
> two of the Base Guide on it: Planning/Designing your Database. Use it
> to plan what you need. Only then select the database engine you will
> use to create the database.
I don't see why. The engine should be irrelevant to the plan. At least,
so long as you choose a relational database, except for a few edge
cases, they should all support any decent database plan. The choice of
engine would surely be determined by other factors than the database
plan. MySQL/MariaDB, PostgreSQL, SQLite, Oracle, MSSQL,
Interbase/Firebird etc are all fully competent relational databases, and
so is HSQLDB far as I know, but things like MongoDB aren't. SQLite is a
bit of an anomaly in that it doesn't really do all the stuff of a
proper relational database behind the scenes, but it can still be used
as one.


> Your database is only as good as your plan!
>
>
>
>
> -------- Original message --------
> From: Girvin Herr <girvin.herr@sbcglobal.net> 
> Date:07/10/2014  4:17 PM  (GMT-05:00) 
> To: users@global.libreoffice.org 
> Subject: Re: [libreoffice-users] Adding fields to existing Base table 
>
> Dave,
> I use the MySQL server and have added table fields before.  It is
> simple in the LO Base table design window.
> Tables -> right click on table -> select Edit
> MySQL accepts these changes and adds the field.  Of course, then you 
> must add the new field toyour forms, queries, relationships and 
> reports, as needed.
>
> Deleting fields is another matter.  I have had problems with MySQL
> field deletions before - especially messing with the primary key.
> Then it may be a matter of directly using the MySQL interface
> program, with some SQL code, bypassing LO Base.  But it is possible.
> So be careful what you do.  Think it out first.
>
> I have no experience with the internal HSQLDB server.  From what I
> read on this forum, using the internal HSQLDB server with a large 3GB 
> database is not recommended.  Best to use MySQL, MariaDB, or
> Postgresql for such large databases.  Also, remember that there may
> be no way to easily port your data from the internal HSQLDB database
> to an external database if you so decide to make the change in the
> future.  You may end up re-entering it all.  Best to make the
> commitment now, before you run into a wall.  Note that I consider my
> databases "large" at 2.7_M_B for the current largest.  IMHO, 3GB is
> out there.
>
> HTH.
> Girvin Herr
>
>
> On 07/10/2014 12:27 PM, dave boland 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