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 <paulste...@afrihost.co.za> wrote: > Just a few thoughts... > > On Tue, 15 Jul 2014 19:30:47 -0400 > elderdanlewis <elderdanle...@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.h...@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+unsubscr...@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+unsubscr...@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
