Abbreviation List Tables Design, aka OTLT

#41

Quote:

That's where there is an issue. Just what is "pure" database design?
Many today mean the Relation Model when they use this term. Is there
something else you are referring to as "pure"?

I was just tying to say that the focus should mostly be on a good database
design, and not be overly influenced by physical contraints. For example:
"version A causes more disk I/O than version B, so we'll use version B even
though it is much more difficult to maintain and coding it will be a bear."

Winging it, here would be my basic rules to determine a "good" application
and database design:

1 - Users actually use the system (this implies that the system does what
they want and runs "fast enough")

2 - Functional change requirements are not met with the sounds of wailing
and the gnashing of teeth by the IT staff

So ultimately, if your users are happy, then that is the only measurement
that really matters.

#42

On Mon, 23 Jan 2006 14:59:49 -0800, "Brian Bond"
<brian (AT) XYZXYZXYZXYZbonanzapress (DOT) com> wrote:

<snip>

Quote:

Winging it, here would be my basic rules to determine a "good" application
and database design:

1 - Users actually use the system (this implies that the system does what
they want and runs "fast enough")

2 - Functional change requirements are not met with the sounds of wailing
and the gnashing of teeth by the IT staff

Ah yes, but.....

That's all AFTER the sale. All of our (collective MV) problems
start before the purchase..... The loudest voice wins..... or,
alternatively, the "Never heard of it. Can't be any good" comes into
play... then.....

Quote:

So ultimately, if your users are happy, then that is the only measurement
that really matters.

And, if so many of the MV user base are happy, why has nobody ever
heard of it/accepted the rationale of the MV database theory?

Because our users, per se, are a quiet lot. Not looking for
anything new. Not asking for anything else... Just going on, from day
to day.... Not involved... Oblivious.... Never get onto the radar...

Why would users want to get involved? Their job's being done. Why
should they have any further involvement? Why would they want to?

Users should be made to fill in a form from the Thought Police giving
their responses to a number of questions on a "before you buy any
application software" board, which board should then be a compulsory
"check point" for anybody in the market for any application software!

Marketing? Bah! Humbug! <G>

Quote:

Regards,

Bruce Nichol
Talon Computer Services
ALBURY NSW Australia

http://www.taloncs.com.au

If it ain't broke, fix it until it is....

#43

Brian Bond wrote:

Quote:

That's where there is an issue. Just what is "pure" database design?
Many today mean the Relation Model when they use this term. Is there
something else you are referring to as "pure"?

I was just tying to say that the focus should mostly be on a good database
design, and not be overly influenced by physical contraints. For example:
"version A causes more disk I/O than version B, so we'll use version B even
though it is much more difficult to maintain and coding it will be a bear."

Winging it, here would be my basic rules to determine a "good" application
and database design:

1 - Users actually use the system (this implies that the system does what
they want and runs "fast enough")

2 - Functional change requirements are not met with the sounds of wailing
and the gnashing of teeth by the IT staff

So ultimately, if your users are happy, then that is the only measurement
that really matters.

I agree that if a) the end users b) the s/w development users (those
maintaining software) and c) the budget officer who pays for the system
over time are all happy, then you have a good design.

If we zero in on the s/w design aspects that would be termed "logical
data modeling" then this OTLT is only one example where we have
options. It seems to me that within the MV world, one of these options
is likely generally better than the other, whether better for a, b, or
c above. I'm trying to figure out which one is the better choice. I
go back and forth on it. Thanks. --dawn

#44

Excalibur wrote:

Quote:

"dawn" <dawnwolthuis (AT) gmail (DOT) com> wrote in message
news:1137879822.638550.14530 (AT) g49g2000cwa (DOT) googlegroups.com...
I'm trying to net it out.

Pros (of using one file of code files):
1) One reads gets you the entire table

Not a major benefit as one only needs it on initialisation and it does have
a big hit on Access report speed

2) All such "codes" are in one file which means
a) they are easy to find
b) it is easier to see the catalog of files and not see a bunch of
files you don't typically want to report against directly
c) you can write an easy query to get all such code/description pairs
and all code files

Definitely a major maintenance benefit no matter how much documentation is
written it invariably winds up that something somewhere gets missed so one
always needs to check the system.

3) It is easier to write an update UI that permits the user to maintain
all such files at once without having to change the code at all when a
new code file is added as a new record.
Very handy

Others?
The same idea is achieved with vastly superior results for reporting by
splitting each item into the individual codes using a unique key. Consider
for every lookup one gets an exact match or a miss whereas for every lookup
with say 10 items in the list one is going to have to use a Basic call and
the n look at 5 items on average to find a match and all 10 to locate a
miss.

OK, so you are suggesting not using separate files and not using a
single record per file, but doing something that those using an RDBMS
often do and make the key a combination of code-file-name and code.
Then you lookup a single row to get a single description.

With this you get most of the benefits of the code file, without using
associated multivalues. This is exactly the design that gets a thumbs
down in the relational world, but I do like it. Thanks. --dawn

#45

Since all of the information is stored in a delimited string, the only real
difference is in the ease or difficulty of remembering, learning, and
maintaining the table or codes. In this case all three 'customers' are the
programmers or whatever you want to call the people who install and maintain
the software. Which of the three is the most important is, like beauty, in
the eyes of the beholder. Those eyes are probably biased by factors like
time available now vs later, money pressure, and a thousand or more devilish
little details that combine to make the discussion more or less academic.
Most of us on this group have the world's worst boss so we tend to take the
quick way while striving for quality and maintainability without bankrupting
the company. Maybe that's why we use MV in the first place.
Bobj
"dawn" <dawnwolthuis (AT) gmail (DOT) com> wrote

Quote:

Excalibur wrote:
"dawn" <dawnwolthuis (AT) gmail (DOT) com> wrote in message
news:1137879822.638550.14530 (AT) g49g2000cwa (DOT) googlegroups.com...
I'm trying to net it out.

Pros (of using one file of code files):
1) One reads gets you the entire table

Not a major benefit as one only needs it on initialisation and it does
have
a big hit on Access report speed

2) All such "codes" are in one file which means
a) they are easy to find
b) it is easier to see the catalog of files and not see a bunch of
files you don't typically want to report against directly
c) you can write an easy query to get all such code/description pairs
and all code files

Definitely a major maintenance benefit no matter how much documentation
is
written it invariably winds up that something somewhere gets missed so
one
always needs to check the system.

3) It is easier to write an update UI that permits the user to maintain
all such files at once without having to change the code at all when a
new code file is added as a new record.
Very handy

Others?
The same idea is achieved with vastly superior results for reporting by
splitting each item into the individual codes using a unique key.
Consider
for every lookup one gets an exact match or a miss whereas for every
lookup
with say 10 items in the list one is going to have to use a Basic call
and
the n look at 5 items on average to find a match and all 10 to locate a
miss.

OK, so you are suggesting not using separate files and not using a
single record per file, but doing something that those using an RDBMS
often do and make the key a combination of code-file-name and code.
Then you lookup a single row to get a single description.

With this you get most of the benefits of the code file, without using
associated multivalues. This is exactly the design that gets a thumbs
down in the relational world, but I do like it. Thanks. --dawn

#46

Couple of points..

Firstly, you don't hear of MV too much cos it's sold as part of the
application. Whereas, the Relational Databases (SQLServer and Oracle
particularly) are sold as "here's the tool, now build your solution on it".
None of the MV vendors market like that.

Secondly, I don't agree with you that one or other approach is best. I use
both in my system, it just depends on how the lookup will be used... I'm
actually designing integration with a third party database (which is
relational and I'm importing into a series of MV files) whilst all of the
tables have the same short layout (a code, a cross reference and a
description) I've imported them in using a mixture of the approaches
depending on how I'm going to use the data..

So as other posters have mentioned, neither is inherently generally "right"
or "wrong" - you have to choose the best method for the circumstance.
The single record multi-valued lookup is a great case in point to define why
MV gives you more options than relational databases....

Just a few more Euros worth.

Regards
Simon
"dawn" <dawnwolthuis (AT) gmail (DOT) com> wrote

Quote:

Brian Bond wrote:
That's where there is an issue. Just what is "pure" database design?
Many today mean the Relation Model when they use this term. Is there
something else you are referring to as "pure"?

I was just tying to say that the focus should mostly be on a good
database
design, and not be overly influenced by physical contraints. For example:
"version A causes more disk I/O than version B, so we'll use version B
even
though it is much more difficult to maintain and coding it will be a
bear."

Winging it, here would be my basic rules to determine a "good"
application
and database design:

1 - Users actually use the system (this implies that the system does what
they want and runs "fast enough")

2 - Functional change requirements are not met with the sounds of wailing
and the gnashing of teeth by the IT staff

So ultimately, if your users are happy, then that is the only measurement
that really matters.

I agree that if a) the end users b) the s/w development users (those
maintaining software) and c) the budget officer who pays for the system
over time are all happy, then you have a good design.

If we zero in on the s/w design aspects that would be termed "logical
data modeling" then this OTLT is only one example where we have
options. It seems to me that within the MV world, one of these options
is likely generally better than the other, whether better for a, b, or
c above. I'm trying to figure out which one is the better choice. I
go back and forth on it. Thanks. --dawn

#47

Simon Verona wrote:
<snip>

Quote:

Secondly, I don't agree with you that one or other approach is best. I use
both in my system, it just depends on how the lookup will be used...

That all sounds well and good, but, frankly I haven't seen a lot of
different uses of your basic abbreviation-description "code files," aka
"lookup files" since 1977 when I created my first one. They are used
for data quality and consistency on the way into the system and then
translating back to the description for nicer output. I'm sure there
are variations on this, but I don't think that figuring out "the
average code table" will be used needs to take a lot of brain cycles at
this point, right?

Quote:

I'm
actually designing integration with a third party database (which is
relational and I'm importing into a series of MV files) whilst all of the
tables have the same short layout (a code, a cross reference and a
description) I've imported them in using a mixture of the approaches
depending on how I'm going to use the data..

But you are likely deciding between whether this will be a pretty
standard code file or something other than that. I'm big on
understanding requirements and all, but there are always some basic
lookup files and it seems to me that it is either generally better to
put these into one format or another. It is unlikely that they are all
equally beneficial. The difference might be small, however.

Quote:

So as other posters have mentioned, neither is inherently generally "right"
or "wrong" - you have to choose the best method for the circumstance.

pish posh

[no, I have never written that before nor do I know what it means]

Quote:

The single record multi-valued lookup is a great case in point to define why
MV gives you more options than relational databases....

Just a few more Euros worth.

I'll take 'em. I've not had a Euro in my hand as yet. Cheers! --dawn

Quote:

Regards
Simon
"dawn" <dawnwolthuis (AT) gmail (DOT) com> wrote in message
news:1138061541.096841.274760 (AT) z14g2000cwz (DOT) googlegroups.com...

Brian Bond wrote:
That's where there is an issue. Just what is "pure" database design?
Many today mean the Relation Model when they use this term. Is there
something else you are referring to as "pure"?

I was just tying to say that the focus should mostly be on a good
database
design, and not be overly influenced by physical contraints. For example:
"version A causes more disk I/O than version B, so we'll use version B
even
though it is much more difficult to maintain and coding it will be a
bear."

Winging it, here would be my basic rules to determine a "good"
application
and database design:

1 - Users actually use the system (this implies that the system does what
they want and runs "fast enough")

2 - Functional change requirements are not met with the sounds of wailing
and the gnashing of teeth by the IT staff

So ultimately, if your users are happy, then that is the only measurement
that really matters.

I agree that if a) the end users b) the s/w development users (those
maintaining software) and c) the budget officer who pays for the system
over time are all happy, then you have a good design.

If we zero in on the s/w design aspects that would be termed "logical
data modeling" then this OTLT is only one example where we have
options. It seems to me that within the MV world, one of these options
is likely generally better than the other, whether better for a, b, or
c above. I'm trying to figure out which one is the better choice. I
go back and forth on it. Thanks. --dawn

#48

Well, the problem for me is size of the record... MV is still not very
good at churning large records around in memory.... Hence why I make the
differentiation between large and small lookup lists. I don't mind reading
in a lookup list of 100 in one read, but for 1000 it wouldn't be so
practical.

By the same token, you are unlikely to use a drop-down lookup for 1000 items
whereby you might for 100... For 1000, you would ask for a code and then
validate it (this is where the STATES*XX lookup is more efficient as you
still only need one disk read to validate but you have no requirement for
the whole lookup table at once.)

Not sure how to state that mathematically though! The key thing in my mind
is whether you'd ever need the whole lookup table at once in your
application. Drop-down list boxes is the key scenario where you would have
this.

Hope I'm making some sense!

Simon

Regards
Simon
"dawn" <dawnwolthuis (AT) gmail (DOT) com> wrote

Quote:

Simon Verona wrote:
snip
Secondly, I don't agree with you that one or other approach is best. I
use
both in my system, it just depends on how the lookup will be used...

That all sounds well and good, but, frankly I haven't seen a lot of
different uses of your basic abbreviation-description "code files," aka
"lookup files" since 1977 when I created my first one. They are used
for data quality and consistency on the way into the system and then
translating back to the description for nicer output. I'm sure there
are variations on this, but I don't think that figuring out "the
average code table" will be used needs to take a lot of brain cycles at
this point, right?

I'm
actually designing integration with a third party database (which is
relational and I'm importing into a series of MV files) whilst all of
the
tables have the same short layout (a code, a cross reference and a
description) I've imported them in using a mixture of the approaches
depending on how I'm going to use the data..

But you are likely deciding between whether this will be a pretty
standard code file or something other than that. I'm big on
understanding requirements and all, but there are always some basic
lookup files and it seems to me that it is either generally better to
put these into one format or another. It is unlikely that they are all
equally beneficial. The difference might be small, however.

So as other posters have mentioned, neither is inherently generally
"right"
or "wrong" - you have to choose the best method for the circumstance.

pish posh

[no, I have never written that before nor do I know what it means]

The single record multi-valued lookup is a great case in point to define
why
MV gives you more options than relational databases....

Just a few more Euros worth.

I'll take 'em. I've not had a Euro in my hand as yet. Cheers! --dawn

Regards
Simon
"dawn" <dawnwolthuis (AT) gmail (DOT) com> wrote in message
news:1138061541.096841.274760 (AT) z14g2000cwz (DOT) googlegroups.com...

Brian Bond wrote:
That's where there is an issue. Just what is "pure" database
design?
Many today mean the Relation Model when they use this term. Is
there
something else you are referring to as "pure"?

I was just tying to say that the focus should mostly be on a good
database
design, and not be overly influenced by physical contraints. For
example:
"version A causes more disk I/O than version B, so we'll use version B
even
though it is much more difficult to maintain and coding it will be a
bear."

Winging it, here would be my basic rules to determine a "good"
application
and database design:

1 - Users actually use the system (this implies that the system does
what
they want and runs "fast enough")

2 - Functional change requirements are not met with the sounds of
wailing
and the gnashing of teeth by the IT staff

So ultimately, if your users are happy, then that is the only
measurement
that really matters.

I agree that if a) the end users b) the s/w development users (those
maintaining software) and c) the budget officer who pays for the system
over time are all happy, then you have a good design.

If we zero in on the s/w design aspects that would be termed "logical
data modeling" then this OTLT is only one example where we have
options. It seems to me that within the MV world, one of these options
is likely generally better than the other, whether better for a, b, or
c above. I'm trying to figure out which one is the better choice. I
go back and forth on it. Thanks. --dawn

#49

Simon Verona wrote:

Quote:

Well, the problem for me is size of the record... MV is still not very
good at churning large records around in memory.... Hence why I make the
differentiation between large and small lookup lists. I don't mind reading
in a lookup list of 100 in one read, but for 1000 it wouldn't be so
practical.

By the same token, you are unlikely to use a drop-down lookup for 1000 items
whereby you might for 100... For 1000, you would ask for a code and then
validate it (this is where the STATES*XX lookup is more efficient as you
still only need one disk read to validate but you have no requirement for
the whole lookup table at once.)

Not sure how to state that mathematically though! The key thing in my mind
is whether you'd ever need the whole lookup table at once in your
application. Drop-down list boxes is the key scenario where you would have
this.

Hope I'm making some sense!

Yes -- that gave me some good insight into the rationale for various
approaches. While it is (typically) considered very bad database
design to think about the UI when modeling the data, that is what often
is done (for better or worse, I don't have an opinion on that yet).
So, you might put all lookup lists that might end up as drop-downs into
a single file, reusing all of the same code no matter which lookup list
is needed and then for larger lookup lists, put them in separate files
and use different reusable components for those.

Such an approach seems effective, maintainable, usable, and considered
poor design in theory because you have coupled your database design in
some small way to your UI design. Vewy intewesting. thanks. --dawn

#50

I understand the theory of not worrying about the user when designing your
database, but imho it's this that causes many development projects to run
over time and budget... Too much time is spent on designing the database,
which is inflexible and slow when building the application..

To me, it seems logical to think of the likely ways that data will be used
and incorporating that into your database design methodology!

A few more euros your way!

Simon
"dawn" <dawnwolthuis (AT) gmail (DOT) com> wrote

Quote:

Simon Verona wrote:
Well, the problem for me is size of the record... MV is still not very
good at churning large records around in memory.... Hence why I make the
differentiation between large and small lookup lists. I don't mind
reading
in a lookup list of 100 in one read, but for 1000 it wouldn't be so
practical.

By the same token, you are unlikely to use a drop-down lookup for 1000
items
whereby you might for 100... For 1000, you would ask for a code and
then
validate it (this is where the STATES*XX lookup is more efficient as you
still only need one disk read to validate but you have no requirement for
the whole lookup table at once.)

Not sure how to state that mathematically though! The key thing in my
mind
is whether you'd ever need the whole lookup table at once in your
application. Drop-down list boxes is the key scenario where you would
have
this.

Hope I'm making some sense!

Yes -- that gave me some good insight into the rationale for various
approaches. While it is (typically) considered very bad database
design to think about the UI when modeling the data, that is what often
is done (for better or worse, I don't have an opinion on that yet).
So, you might put all lookup lists that might end up as drop-downs into
a single file, reusing all of the same code no matter which lookup list
is needed and then for larger lookup lists, put them in separate files
and use different reusable components for those.

Such an approach seems effective, maintainable, usable, and considered
poor design in theory because you have coupled your database design in
some small way to your UI design. Vewy intewesting. thanks. --dawn