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

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.

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

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.

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

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/

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.

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.


Just FYI :)


Paul

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