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 <[email protected]> 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: [email protected] 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
