![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
If you are going to create a database, you plan it THOROUGHLY! Beginning with a small simple database and expanding it to a very large one is only asking for a complete disaster! Unless you are using a flat database, your tables need to be in at least fourth normal form. 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. 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. Your database is only as good as your plan!
I agree that planning is key. I started with a spreadsheet until the
data source was stable. The plan is to import the data into Base, do
some reports, report my progress, then gather comments and suggestions.
My guess is that the database will grow - I just don't know to what.
So, I may end up exporting the tables to spreadsheet and re-import.
Once the database is stable, I plan to move to either MySQL or MariaDB.
Dave,
Just a few thoughts...
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.
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