Re: [libreoffice-users] Currently Using Spreadsheet for Personal Project - Thinking About Database

Paul wrote:
> So I was interested, and did a little more digging. I figured I would
> share the info.
>
>
> On Sun, 10 Aug 2014 22:48:43 +0100
> Mark Bourne <libreoffice-ml.mbourne@spamgourmet.com> wrote:
>
> > I don't know to be honest, not having a lot of experience in database
> > design. I've only ever really used MySQL, which does support enums,
> > but maybe that's just a MySQL feature.
>
> Seems like it is a MySQL specific feature, although PostgreSQL seems to
> support it.

Ah, so definitely best avoided if portability is a concern! And probably 
advisable to avoid even if portability isn't an immediate concern.

> > To me, the set of possible status values just seems more like part of
> > the database schema design than data entry - you wouldn't generally
> > add or remove status options (and the application may assign special
> > meaning to certain statuses, so it may be critical that a specific
> > set of values is defined). Then again, in some applications being
> > able to introduce new statuses at any time could be an advantage.
>
> Yeah, being able to change these values later is one of the main
> reasons to use a separate table. Clients almost *always* end up adding
> or removing some of these.

The database work I've done has been for either myself or small 
voluntary organisations I'm involved in, so I've never really had to 
deal with that. Where the set of values could obviously be expanded 
(e.g. to indicate colour of something) I'd certainly do that with a 
table containing the options. My main use for enums has been to indicate 
things like male/female gender, allowing null if I need to allow for an 
unknown value (point taken - someone might would want to expand that 
list of options...)

> Also, when you have specific functionality tied to some of these
> statuses, it's always a good idea to add a flag field for it, and make
> your code check if the flag is on, rather than if the status name
> matches a specific value, so that if the client ends up deciding (as
> they invariably will) that actually a second status must also do that
> thing that they assured you only the one status would ever do, then you
> simply turn that flag on for the second status, rather than having to
> change your code base and hunt for all instances of checking for the
> status by name.

One of the places I have used enums was for that kind of a flag, where 
there were three options for how the application could handle a record. 
Essentially an event booking database where each participant was 
assigned to one of about 10 groups, and each group needed to be handled 
by the application in one of three different ways (displaying different 
sets of options), so I had a "group type" enum (with three possible 
values) on the groups table indicating to the application how 
participants in that group should be handled.

Looking back, I actually did end up writing code into the application to 
decode that "group type" into a set of flags indicating which parts of 
the GUI should be displayed and which options should be offered - with 
the intention of porting that into a database table. I just never got 
round to doing that before the event was over.

So it seems I'm violently agreeing with you!

> Also, then you can localize your status names if you ever want to make
> your program support other languages.

Something else I haven't had to worry about, but if I did I'd have done 
the localisation in the application - the enum values (as I use them 
anyway) are not generally displayed as-is to the user anyway, they're 
just convenient names to use in the database. Using localised status 
names in the database table surely hinders localising the application - 
if you wanted to connect both English and French front-end applications 
to the same back-end database, wouldn't they both get the same status 
names to display, rather than the names appropriate for the application 
language? Or would the status table in that case contain strings in one 
language (or perhaps even generic keywords) for which the application 
would look up translations in it's own localisation table?

> The page here gives some benefits and pitfalls, but I don't see any of
> the benefits as not being provided by the separate status table
> solution:
> https://www.udemy.com/blog/mysql-enum/
>
> This page also gives a few more reasons why you shouldn't use them, and
> prefers "reference tables", which I called status tables:
> http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/

Some interesting points there, thanks.

> While most of those reasons may not apply to this project, the two that
> do for me are:
>
> 1) The statuses are data, and shouldn't be stored as structure. This is
> a conceptual thing, but it's best to start out thinking the right way.

In this particular case with statuses, I could go either way and do see 
your point. I might even go with having "date started" and "date 
finished" fields and determining whether a book is unread, in progress 
or completed from those.

More generally, I'd certainly use a table where the list of options is 
subject to change and doesn't have any special meaning to the 
application (e.g. colours or continents). For gender I've previously 
used an enum, but in light of this discussion will probably do that 
differently in future; an application shouldn't care about a third 
option being added.

Now it comes to it, I'm not sure I've got any uses for enums left... 
Possibly when each new option requires explicit support in the 
application - from the point of view of modifying things in future, it's 
more obvious that adding a new possible value to an enum column 
(essentially changing the database structure) might cause problems for 
the application, and not so obvious that adding a new row to a reference 
table might cause problems for the application. But I'm less convinced 
than I was that this can't be handled in a different way, e.g. with a 
set of boolean flags.

> 2) It makes populating the dropdowns way easier, unless you were going
> to hard-code them, which is admittedly even easier, but can lead to
> problems down the road when you want to make changes or additions.

Can't argue with that. When I have used enums, the list of options in 
the UI and mapping between those and database enum values has been 
hard-coded.

> Just FYI :)
>
>
> Paul

Thanks! It's certainly been useful to see better ways of doing things.

Mark.

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