f



DB design: storing "this" or "that" (..or "the other")?

[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.
0
David
6/16/2011 1:58:50 PM
comp.databases.mysql 6944 articles. 1 followers. laredotornado (869) is leader. Post Follow

14 Replies
820 Views

Similar Articles

[PageSpeed] 18

David wrote:
> 
> [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.


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

0
tnp (2409)
6/16/2011 2:09:07 PM
On 2011-06-16 16:09, The Natural Philosopher wrote:
> David wrote:
>> 
>> [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
>> 

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;


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

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.

[...]

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

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

/Lennart

0
6/16/2011 9:44:51 PM
On 6/16/2011 9:58 AM, David wrote:
>
> [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.

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@attglobal.net
==================
0
jstucklex (14659)
6/17/2011 12:25:23 AM
> I'm about to create a table to extend a database to allow users to store 

Why _create a table_ ?  An obvious thing to do is _add a column to a table_,
with an additional table (or two) maybe needed or maybe not.

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

Are you sure the choice will never be "choose 7 of 9" (and no, I
don't mean the Star Trek character)?

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

This works.  You need to add this to an existing table, or to also
have a user id column in a new table, otherwise you end up with a
single choice for the entire website, not one choice for each user.
I really hope you're not in a situation of not being able to modify
the existing table because adding a column (even at the end) breaks
existing code.

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

You could make then "choice" field into a foreign key for a new
table (e.g. "Payment_methods") that lists all the choice codes and
their descriptions.  For example, if "choice" is really "payment
method", you might have "0" is PayPal and "1" is "gift subscription".
Later you might add "2" for MasterCard, "3" for Visa, and "4" for
debit card.  A web application can use the Payment_methods table
to ask the user to choose, and check that the input corresponds to
one of the available choices.  You add or delete entries to the
table, rather than modifying the application, that asks the user
for payment method.  You will still obviously have to modify the
payment module to know how to charge money via the new payment method.

Incidentally, you might have more columns in the Payment_methods
table so you can phase in or phase out payment methods (the choices
shown on a menu and the choices processed may be different, due to
things like a "offer this on the menu" or "for corporate memberships
only" flags).  For example, "mailed-in-cash" might be accepted for
existing customers using that option ("grandfathered"), but not
offered to new ones while you send out notices that you are
discontinuing accepting mailed cash and they have to choose a new
payment method within 6 months.

Another possibility is to use enums instead of integers for the column
value.  A disadvantage is that you have to change the schema every time
you need to add a new value.  The advantage is that they are a bit
more readable than code numbers by admins and DBAs, but you aren't
really supposed to be designing raw database content for readability
by admins and DBAs.

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

This creates a problem that you might end up with more than one of
them set to 1 (or none of them).  It also has the problem that you
have to modify the application to use the new columns.  If every application
is checking that the choices are set properly, you may have to modify
all parts of the application *ALL AT ONCE*, since any user who has taken
the new choice will appear to be a user who has taken *NO* choice to
a part of the application that hasn't been updated yet to know about the
new field.

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

But you have to add the new field name to every query involving them.


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

There's tidily-extensible and there's dangerously-extensible.  What
would be the consequences of somehow accidentally choosing all the
choices (or none of them)?  If the customer gets quadruple-billed,
that's bad.  If there's several bonuses available on an order and
you will go broke if you give them all out on every order, that's
bad.  If a customer accidently selects notification by both email
and text if they get a message for them on the web site, that's
probably rather harmless, and they may be able to fix it themselves
if they don't like it.

You can be reasonably sure that a user will use only one payment
method (at any given time - users may change payment methods
occasionally).  I don't know the nature of your choices so I can't
comment on your situation.

0
6/17/2011 6:06:47 AM
On 2011-06-16, Lennart Jonsson <erik.lennart.jonsson@gmail.com> wrote:
> On 2011-06-16 16:09, The Natural Philosopher wrote:
>> David wrote:
>>> 
>>> 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
>>> 
>
> 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;


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.


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

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


>> Have a look at enumerated variables. I believe they are an efficient and 
>> extensible way to store multiple exclusive choices.
>>
>
> Enums are dangerous and confusing creatures at the database layer and
> one should think twice before summing them (IMHO).

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.

0
David
7/6/2011 11:14:17 AM
On 2011-06-17, Jerry Stuckle <jstucklex@attglobal.net> 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.

Thanks, Jerry. This is essentially the same as Lennart suggested.


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

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.

0
David
7/6/2011 11:22:20 AM
On 2011-06-17, Gordon Burditt <gordonb.q44sw@burditt.org> wrote:
>> I'm about to create a table to extend a database to allow users to store 
>
> Why _create a table_ ?  An obvious thing to do is _add a column to a table_,
> with an additional table (or two) maybe needed or maybe not.

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.


>> (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).
>
> Are you sure the choice will never be "choose 7 of 9" (and no, I
> don't mean the Star Trek character)?

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


>> 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).
>
> This works.  You need to add this to an existing table, or to also
> have a user id column in a new table, otherwise you end up with a
> single choice for the entire website, not one choice for each user.

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

> I really hope you're not in a situation of not being able to modify
> the existing table because adding a column (even at the end) breaks
> existing code.

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.


>> 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).
>
> You could make then "choice" field into a foreign key for a new
> table (e.g. "Payment_methods") that lists all the choice codes and
> their descriptions.  
[..]

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").. ;-)


>> Or instead, I could define 'n' number of pseudo-boolean fields (with 0 or 1 
>> values):
>
> This creates a problem that you might end up with more than one of
> them set to 1 (or none of them).  

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?

> It also has the problem that you
> have to modify the application to use the new columns.  

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.

> If every application
> is checking that the choices are set properly, you may have to modify
> all parts of the application *ALL AT ONCE*, 

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

> since any user who has taken
> the new choice will appear to be a user who has taken *NO* choice to
> a part of the application that hasn't been updated yet to know about the
> new field.

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?


>> * 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.
>
> But you have to add the new field name to every query involving them.

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?!)


>> 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?
>
> There's tidily-extensible and there's dangerously-extensible.  What
> would be the consequences of somehow accidentally choosing all the
> choices (or none of them)?  

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.

0
David
7/6/2011 1:10:26 PM
On 7/6/2011 7:22 AM, David wrote:
> On 2011-06-17, Jerry Stuckle<jstucklex@attglobal.net>  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.
>
> Thanks, Jerry. This is essentially the same as Lennart suggested.
>
>
>> 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.
>
> 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.
>

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@attglobal.net
==================
0
jstucklex (14659)
7/6/2011 2:28:11 PM
On 7/6/2011 10:28 AM, Jerry Stuckle wrote:
> There are a number of tools out there which can create a diagram from an
> existing database.


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

Bill B

0
me4 (19624)
7/6/2011 2:49:34 PM
Am 2011-07-06 16:49, Bill B meinte:
> On 7/6/2011 10:28 AM, Jerry Stuckle wrote:
>> There are a number of tools out there which can create a diagram from an
>> existing database.
> 
> 
> Any recommendations, Jerry? I have looked for the likes of this but have
> only found apps that are bloated and kludgy.

MySQL Workbench. It's bloated (particularly on Windows), but free. The
others I know are bloated and not free.

Gregor

-- 
http://vxweb.net
0
usenet2181 (209)
7/6/2011 4:24:14 PM
On 7/6/2011 10:49 AM, Bill B wrote:
> On 7/6/2011 10:28 AM, Jerry Stuckle wrote:
>> There are a number of tools out there which can create a diagram from an
>> existing database.
>
>
> Any recommendations, Jerry? I have looked for the likes of this but have
> only found apps that are bloated and kludgy.
>
> Bill B
>

As Gregor indicated, MySQL Workbench.  I use Visio, but that's because I 
have it for other things, also.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
0
jstucklex (14659)
7/6/2011 6:32:13 PM
On 7/6/2011 10:49 AM, Bill B wrote:
> On 7/6/2011 10:28 AM, Jerry Stuckle wrote:
>> There are a number of tools out there which can create a diagram from an
>> existing database.
>
>
> Any recommendations, Jerry? I have looked for the likes of this but have
> only found apps that are bloated and kludgy.
>
> Bill B
>

I should also add for something like this bloatware isn't as bad as 
other times.  I don't need to use it much.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
0
jstucklex (14659)
7/6/2011 6:33:18 PM
On 2011-07-06 16:49, Bill B wrote:
> On 7/6/2011 10:28 AM, Jerry Stuckle wrote:
>> There are a number of tools out there which can create a diagram from an
>> existing database.
> 
> 
> Any recommendations, Jerry? I have looked for the likes of this but have 
> only found apps that are bloated and kludgy.

Have a look at Schemaspy (http://schemaspy.sourceforge.net/).


/Lennart
0
7/7/2011 5:38:52 AM
On 2011-07-06 13:14, David wrote:
[...]
> 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).
> 

You can use different types of table in a database, so even if all other
tables are myisam you can create innodb tables

> "REFERENCES" I didn't know about, so obviously there's some reading for me 
> to do there, 

It's a shorter version of:

foreign key ...
    references ...

or

constraint ... foreign key ... references ...

I actually prefer the last one since one can name the constraint that way.

but if you are stuck with myisam it won't do you any good. One highly
annoying "feature" with myisam is that it gladly accepts the syntax, but
silently ignores it.

[...]

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

It might have been a badly chosen word, but the meaning is that you have
a set of values that defines a domain. Example:

create table genders (
    gender_code smallint not null primary key
);

-- as defined in ISO-5218
insert into genders (gender_code) values (0),(1),(2),(9);



Now you can reference genders from for example your employees table:

create table employees (
    ...
    gender_code smallint not null references genders (gender_code),
    ...
);


In some sense genders is the domain for gender_code


/Lennart
0
7/7/2011 6:20:23 AM
Reply: