Date: prev next · Thread: first prev next last
2014 Archives by date, by thread · List index


I did not write my comments as theory. I did it from personal experience! Relational databases have 
to have tables that have been normalized period! Without doing this, any data obtained from a query 
or report can NOT be trusted for accuracy.

Besides the OP was the one mentioning a 3 GB database by the end of the year. Anything that large 
requires large amounts of planning. Would you want to drive a car knowing that it had not been 
carefully planned before being built? Working with a 3 GB database without careful plans doesn't 
any more sense than this.

Dan

-------- Original message --------
From: Tom Davies <tomcecf@gmail.com> 
Date:07/16/2014  7:48 AM  (GMT-05:00) 
To: Paul <paulsteyn1@afrihost.co.za> 
Cc: users@global.libreoffice.org 
Subject: Re: [libreoffice-users] Adding fields to existing Base table 

Hi :)
There is a big difference between theory and practice.  That doesn't mean
either is wrong.  Finding a good balance between them is probably the best
aim. 

There has to be a balance that gets something that is "good enough" to work
with but with enough good planning and good sense to make the database
flexible enough for future developments.

Avoiding repetition of data is a good aim but can easily be taken tooo
far.

It's when individual fields are taken out of the main table to be used as
separate tables that it can get a tad confusing imo.  It helps minimise
typos and that makes entries that should be identical really be identical.
Sometimes that is crucial.  Mostly it doesn't really matter too much.  If
some fields can be grouped together that can sometimes be easier to
understand as a separate table.

Also it is possible that some fields might have something like only 1%  of
it's data being repeated.

However a lot of this all becomes clearer when making a prototype and
seeing what real-world data starts filling the tables.

"All plans fail in the face of the enemy" but that doesn't mean it's ok to
not plan.  "Failing to plan is planning to fail".
Regards from
Tom :)



On 16 July 2014 02:50, Paul <paulsteyn1@afrihost.co.za> wrote:

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



-- 
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

-- 
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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.