[I realise that this isn't necessarily a question specific to MySQL, but
it's the database system that I'm using..]


I'm about to create a table to extend a database to allow users to store
(via a webform) a particular preference choice setting in the database.

At present, the options available to users will be that they can choose:

* this
xor
* that

(ie, only 1 of 2 possible values).


But I suppose that at some point in the future, we might decide to let users
choose 1 from an increased range of choices (1 from n).

I was wondering what best practice thinking was on how best to deal with this?

With only 1 of 2 choices, effectively 1 boolean choice, I could define a
single field 'choice' (obviously not the real name) and store the values as
0 or 1 (as long as I clearly define for my application how these map to one
choice versus the other).

Were extra choice possibilities to become available, this would obviously
not work, but instead I could redefine my 'choice' field with acceptable
values of 0, 1, 2, ..n (or perhaps as strings 'this', 'that', 'the other',
...'the last choice', which although perhaps more self-explanatory when a
human admin needs to read the database would probably make for messier
application coding).

Or instead, I could define 'n' number of pseudo-boolean fields (with 0 or 1
values):

* this_set
* that_set
* the_other_set
...
* the_last_choice_set

This way, it should be clear from the field names what they mean, and easy
to determine whether they are set or not.


The last suggestion seems to me to perhaps be the most tidily-extensible way
to do it (and would perhaps most easily allow users to choose any
combination of 'this', 'that', ..'the last choice' rather than just *one*,
were the rules to change), but I was wondering if anybody else had any
thoughts about this?

Thanks,


David.

David wrote:

Have a look at enumerated variables. I believe they are an efficient and
extensible way to store multiple exclusive choices.

You store and return e.g 'yes' 'no' 'maybe' and so on. Just beware you
can also store 'null' and that's caught me out as well.. I cant remember
the details, but null fails the test of 'not=yes' IIRC. You have to
explicitly test for it..

On 2011-06-16 16:09, The Natural Philosopher wrote:
some food for thought

create table possible_choices (
choice char(4) not null primary key
) engine = innodb;

insert into possible_choices (choice) values ('this'),('that');

create table answers (
user ...
choice char(4) not null
references possible_choices (choice),
primary key (user, choice)
) engine = innodb;


insert into possible_choices (choice) values ('bang'),('buck');

Typically a check constraint is used to define such small domain, but
that is not supported in mysql so I've used a foreign key against a
domain table instead.

[...]

Enums are dangerous and confusing creatures at the database layer and
one should think twice before summing them (IMHO).

/Lennart

On 6/16/2011 9:58 AM, David wrote:
None of the above. Use the relational aspects of the database.

You have one table with the user information and an user_id field.

A second table with a choice_id and choice information (at least a name,
maybe also a description - two or 3 columns total).

A third table has two columns - user_id and choice_id for the selected
options.

And don't worry about the database being "human-readable" - databases
are not made to be human-readable - they are made to store data. If you
want human-readable, you execute the appropriate SELECT statement to get
the information in the format you want.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 2011-06-16, Lennart Jonsson <erik.lennart.jonsson (AT) gmail (DOT) com> wrote:

Thanks for that, I think I'm starting to understand the reasons for having
one table storing the "meaning" of the choice values, and then the
(numeric) "chosen value" elsewhere now..

However, other tables in the system, outwith my control, use MyISAM
tables, so I don't think I'd be able to use InnoDB tables (although the
more I read about them, the more sensible a table type they seem).

"REFERENCES" I didn't know about, so obviously there's some reading for me
to do there, and likewise, I didn't actually know that a PRIMARY KEY could
be made up of more than one field.


I'm afraid that "domain table" is a new term for me, could you perhaps
explain what it means?


Having now read a few webpages about ENUMs, it is similarly appearing
that they may potentially be more trouble than they are worth, and since
they appear not be standard SQL that also seems a good reason not to use
them.


(I'm aware that I'm clearly showing my novicity here, but we all have to
build our knowledge starting with smaller projects (surely better that
than try to build a complex system /thinking/ that you know how it all
should work, only for it to fail spectacularly..), and if it's any
reassurance, this is a fairly small-scale internal project and not
something forming part of a large public-facing online banking system,
for example!)


Thanks,

David.

On 2011-06-17, Jerry Stuckle <jstucklex (AT) attglobal (DOT) net> wrote:
Thanks, Jerry. This is essentially the same as Lennart suggested.


I take your point, and I am sure you are probably right, but often it
certainly _seems_ that it can be easier to to have fields that are "human
readable" to some degree. I "inherited" this database with minimal
documentation, and had to spend quite some time working out how various
tables, mostly with numeric id data (references to ids in other tables)
all actually fitted together, before I could even begin to re-implement
the existing system (moving to a new web platform), let alone add
desired new features!


David.

On 2011-06-17, Gordon Burditt <gordonb.q44sw (AT) burditt (DOT) org> wrote:
Hi Gordon,

My rationale was that while I develop the test website, I'm working with
existing data which will eventually be overwritten with the latest updated
data from the original website, so it seemed to me to be easier to store
this data in a new table, as it relates to an entirely new aspect of the
information that I'm storing in the database, and which doesn't exist
already. The existing database system stores different 'things' (for
want of a better word) in different tables (all 'related' in various
ways), so it seemed sensible to extend this practice.


Indeed not, which is why I mentioned that possibility later in my
message, attempting to keep an eye on how the system might need to
expand..

(In fact, since I first posted, the spec has changed, as the "choice"
preference that I was initially wanting to store was going to be:

* import and display data from source A
xor
* display a link to webpage B

...but due to delays in the development of A, this now puts the final
system on hold temporarily (although I still need to develop my part
of the code in the meantime), and the user-facing front-end will (for
the time being) not show choice A (for the user - although I still
need to write the code to handle this for when this does go live),
and add:

xor
* display data from existing table C
(this option will obviously be quietly dropped again in the future once
it is no longer necessary)

So that just demonstrates the point, I guess..)


Yes, sorry, in my attempt to ask a generalised rather than a specific
question, I'm afraid I rather hand-waved over that implicit
assumption.. ;-(

Fortunately no, because I'm having to re-implement from scratch the PHP
code that will interface with the database, but because of the need to
import existing live data into the development system, I felt that it
would be easier to only have to change the existing table structures as
little as possible.



Since everybody seems to be suggesting this approach, this seems to be
the best way to go (at least where the choice is "1 of n").. ;-)


Obviously I would write code to handle those situations (not least because
you can't trust user input and a malicious user could submit faked form
requests with all kinds of bogus data, and my form handler has to cope
with that), and indeed it could turn out to be the case that a later
change to the system might indeed permit multiple choices to be made. Or
am I perhaps guilty of falling into the trap of trying to foresee
problems that might never arise, when the real solution might be to
"worry then" rather than "worry now" about something that might not ever
happen?

I'd need to modify it anyway in order to add the code to "do" whatever
change the new preference setting was supposed to set (in the website:
in my specific case, display data from one source versus another), once
set (in the form/database), although I can see how just 'one field'
would at least save having to change the form part of it.

Of course, that's why I'm developing the new system on a development
site, which can then just be 'flipped over' when it is ready. Similarly
for any further future changes. (Rule 0: Don't break the live site. ;-) )

Fortunately, it's not as complicated as that in this case. The choice(s)
made by the user only actually affect the output of one page of the
website. If I were to proceed with this idea of "one pseudo-boolean field
per choice-option" then the existing choice options would still be set
appropriately. I'd need to amend the website to add the code to react
accordingly to a possible additional choice option(s) anyway?


I'm afraid I can't think of how else I could handle the possibility of
multiple combinations of choices being set? (Apart from one field
storing (essentially) one of: none, A, B, C, AB, AC, BC, ABC, but that
doesn't scale well if several choices were to become available..)

(Although it's quite possible that there is another solution that just
hasn't occurred to me?!)


Indeed. My form handler code obviously needs to be written in a way so
that it knows what combinations are permissible, and responds
accordingly, before actually storing anything in the database. The issue
of defending against malicious bogus form data also comes into play
again here.


Thanks for your advice,

David.

On 7/6/2011 7:22 AM, David wrote:
Yes, integer values as keys are quite common, because integer comparison
is much more efficient than string comparison.

When I get such a database, the first thing I do is create a diagram of
the database. It makes things much easier to understand.

There are a number of tools out there which can create a diagram from an
existing database.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 7/6/2011 10:28 AM, Jerry Stuckle wrote:

Any recommendations, Jerry? I have looked for the likes of this but have
only found apps that are bloated and kludgy.

Bill B

Am 2011-07-06 16:49, Bill B meinte:
MySQL Workbench. It's bloated (particularly on Windows), but free. The
others I know are bloated and not free.

Gregor

--
http://vxweb.net