Wha'd he say?
The first thing I found when Googling "calculus database design" was
a reference to relational calculus - used in designing relational
databases. The relational folks aren't sure what Multivalued
databases are all about anyway - many probably don't even believe
they really exist - just a figment of someone's imagination. Most of
those who've tried to replace multivalued systems with relational
systems have cost the poor client a fortune, and then failed
miserably. By and large, the group posts I read from DBAs, other
analysts and programmers either didn't know what relational calculus
was, or had learned about it in school and promptly forgot all they
knew when faced with the real world.
I never did well in calculus, and really haven't missed it. Ipso
fatso, I'll keep on doing what works.
;-)
Besides, favorite urban folklore has it that in the 1930s scientists
stated that the lowly Bombus Terristris (bumblebee) couldn't fly. Oh
well, I guess no-one told the bumblebee.
Enjoy Wimbledon, Jim.
Charlie
Jim Idle wrote, On 07/01/2010 08:04 AM:
On
Wed, 30 Jun 2010 08:48:33 +0100
Simon Verona <[email protected]> wrote:
I can see the pro's and cons of both
approaches...
Really? ;-)
I have not weighed in because I am currently in Paris and then on
to London for Wimbledon - so such debates are not top of my list
of things to deal with. However there is fortunately no arguing
with the mathematics and so if you have a multi-value that is not
an unordered list of identical entities, then you are doing it
wrong - it's not my opinion, it is the calculus. The fact that you
can hack up any old data structure in jBC does not mean you are
creating a valid model; it just means that you can make it work.
So, you have three telephone numbers in multi-values - how do you
know which is the work number without externally imposing an
artifical order that does not exist in the calculus? Answer: you
can't. ipso facto, the structure is incorrect.
Jim
I would venture to say that providing the application that is
maintaining the data is itself well written then both approaches
will work fine.
Personally, I don't use jQL/Access/English much...
Telephone nos are stored as an ordered multivalue list (ie mv 1=
Home No, mv 2= work no, mv 3=Mobile etc). Each multivalue is
itself multi-subvalued, with each sub value representing dial
code and then the rest of the number.
I'm aware from a purist perspective this is a horrible use of of
Multi-value, but from a coding perspective we have a parameter
that defines the list of descriptions and the code that prompts
and displays telephone numbers in a simple for/next construct.
It's expandable - if we wanted to add another number to the list
we simply change the parameter table and add an extra
description - job done.
You can reasonably easily create I-Type dictionaries to report
on this construct (though we don't do this).
So reading the above, I guess I'm leaning over to Charlies side
of the fence...
Simon
===========================
Simon Verona
Director
Dealer Management Services Ltd
email: [email protected]
tel: 0845 686 2300
On 29/06/2010 18:02, Daniel Klein wrote:
Interesting reply, Charlie. I had a
feeling that some of what you said would surface.
I, too, have been in this biz along time and I've see a lot of
'weird' data structures. My experiences caused my thinking to
go in a very different direction as we had to deal with moving
data between pick and other (eg COBOL/fixed-field) systems, so
keeping things at the attribute level made this much simpler.
Your whole argument seems to be based on jQL requirements. I
had to consider other factors.
You make a valid point wrt printing multiple phone numbers on
fixed-width media, but doing this when phone numbers are not
MV'd is not really all that tricky. This is not reason enough
for me to change my mind about keeping things segregated as it
seems more intuitive for providing the flexibility to mix,
match and select things...but again, that's just my opinion
based on the same number of years you've been doing this ;-)
We'll have to get together some day and talk about those 50mb
disk drives the size of a desk drawer that were
state-of-the-art!
You said that you like to put data where it will be easy to
select. Other than for the simplest of selections, MVs make
this more complex. For example, let's say you need to select
on certain area codes for (say) one type of phone. Sure, this
is all do-able when you have the associated MV set (with
print limiting and BY-EXP) like you do, but it's rediculously
simple when the area code is in it's own attribute.
You also said that you like to allow for future growth and
expansion. Yes, it's always visually appealing to keep thinks
together. But let's face it, database requirements change and
somewhere down the road the DBA has to add a new field to the
record and shuffling fields to keep things physically together
is just not an option due to the immense application changes
that would be required. Ok, you say you have an associated
'phone type' attribute. What if (someday) you need to add a
'phone extention' and there are no available adjacent
attributes? Uh oh! ;-)
I have to admit, looking at the database design from the
output side of things might cause one to decide on one
structure over another. However, I've always gone by the 'Do
the simplest thing that could possibly work' principle.
All that being said, I guess it all depends on what you are
use to, and the dictates of your customers/users. Perhaps I
was a bit harsh in my original reply. I do use MVs perhaps a
bit more than what I let on, but I always think twice about
it. And I *do* like the challenge of a spirited discussion ;-)
I'm also curious what Jimi has to say about all this.
On Sun, Jun 27, 2010 at 7:40 PM, Charlie Noah
<[email protected] <mailto:[email protected]>>
wrote:
Dan,
Responses below:
On 06-26-2010 9:27 PM, Daniel Klein wrote:
I can answer that one, Charlie.
I worked several years managing mailing lists and it was
always
best to segregate different phone numbers (home,
business,
mobile, fax, etc) into different attributes. In fact, we
even
split each number into 3 attributes, area code, exchange
and last
4-digits (we didn't deal with international) for ease of
doing
ENGLISH/ACCESS/RECALL (take your 'pick' ;-) )
selections. I think
you can see where this is going but if they were all
stored as
multi-values then which MV would be which phone?
Positional data
structures will always lead to more complex coding and
maintenance. And the very last thing you want to do is
add
unnecessary complexity to an already complex
application.
When I design a database I always do it with
English/Access/Recall/JQL in mind. I will use the term
English to
refer to the ad-hoc retrieval system, and Pick to refer to
all the
Multivalue implementations. I know that's inconsistent,
English
belongs to Reality, Access belongs to Pick, but what the
heck,
most old-timers like myself recognize both readily. I much
prefer
to generate reports using English if at all possible. To
that end,
I like to put data where it will be easy to select and
report on.
I also like to allow for future growth and expansion. If
you allow
an attribute for home and work phone, what do you do when
you get
new phone types, like emergency contact, fax, pager, work
cell and
personal cell? There may come new phone number types down
the road
that we've never even heard of. You'll need to allocate
additional
attributes which may not be close to the original phone
numbers.
Even if you leave attributes empty for expansion, you may
not have
enough. When I multivalue phone numbers, I also add an
attribute
for phone type. This way I can associate the 2 attributes,
and use
only 2 columns on a report. Even with the advent of
emailing
reports and browsers, many are still printed on real
printers,
usually limited to 132 characters. Using separate
attributes and
printing them all in one column will call for some
dictionary
trickery. It can be done, but now you have the complexity
you
wanted to avoid in the first place. If you only want to
print work
phone, for instance, you have no problem. Well, neither do
I.
English will handle this quite nicely. I-descriptors add
even more
flexibility.
Which would you prefer, a simple
attribute reference or a complex
F or A correlative (or I-type) just to extract, and
format, the
proper value. And what if someone wanted it formated
with 'dots'
instead of 'dashes'. It really makes managing it much
simpler to
break things up into their smallest (atomic) elements;
very
similar the the OOP approach to coding by having methods
do the
smallest piece of work so that programs can be
constructed, like
an erector set (or tinker toys).
Formatting phone numbers is a piece of cake (or pie, if
that's
your preference). I like to control data entry such that
the user
can enter the phone number in any of several accepted
formats, and
store only the digits. In this way I can format the phone
number
for display or printing as ###-###-####, ###.###.####, ###
###
#### or (###) ###-####, set by the application standards
and
conventions. Non-US numbers can be detected and formatted
properly
as well, using a country code as part of the address. Two
attributes to keep track of, no matter how many phone
types, as
opposed to 2 + (or 6 +) whatever. Print limiting and
exploding
sorts are tools which provide an almost unlimited level of
flexibility. I would hope that every programmer would gain
some
knowledge about both.
We did similar things with names
by breaking them up into 6
attributes: saluation (Mr, Ms, etc), first name, middle
initial,
lastname, suffix (jr, sr, etc), title (eg President).
This
structure gave us the power to mix and match things any
way we
wanted without a lot of effort.
I completely agree here. It is much easier to control the
data as
it is entered, rather than trying to figure out what the
user
decided to put where - the whole name in the first or last
name,
etc. I do the same with addresses (except address 1 and 2,
which
are multivalued). City, state, zip and country code are
all
separate pieces of data.
People think they are clever by
using MVs for everything, but all
they are really doing is creating a giant headache for
the next
person who has to maintain the code and database.
MVs should not be used for everything, especially mixed
data
types, but to not use them where they are appropriate is
to fail
to use the model to it's fullest potential. I've been left
with
many giant headaches by previous programmers, but I can't
recall a
well-designed use of multivalues being among them. This
sounds
like an opinion, which are like elbows, most everyone has
a couple.
Multi-values are best used for
'foreign' references to other
files, NOT for storing 'data'.
Again, opinion. They are great for foreign references, but
not
just for this, as described above.
They are also good for audit types
of data, eg the operator who
handled the record, the date and time it was handled,
the work
order number, etc. This is a perfect example of using a
correlated MV data structure.
I hope you mean associated values in separate attributes
in this
case.
I'm sure there are other valid
uses for MVs but I challenge
anyone to show me how making a multi-valued phone field
is simpler.
I believe that I have shown that there is nothing wrong
with using
multivalued phone numbers, and that it is just as easy or
easier
than what you have described. It is a tribute to the
Multivalue
model that you and I can both make an application work
very well,
each using our own approaches.
As I said earlier, I have designed and built MV
applications for
32 years on most of the major implementations out there
(even Pick
Blue - anyone remember that?). I still consider myself
somewhat
"in training", because I am always learning new methods
and new
ways to look at problems. If not, I would just be
repeating the
first year 32 times. But for Jim to suggest, even somewhat
jokingly, that anyone who uses multivalued phone numbers
needs
retraining, is condescending and inaccurate. In JQL Jim
built the
best implementation of English I have ever worked with (if
he was
the one who built it), yet in most cases he recommends
writing a
program, rather than using JQL. I do not understand this
at all.
Dan
Dan, I have the greatest respect for you and Jim, and
appeal to
you guys for help from time to time. I have found jBASE
(yes, I
used the strange capitalization, even though it is far
more
difficult for a one-handed disabled typist) to be the most
powerful and easiest to use MV implementation I have ever
used. I
hope that you and Jim can agree that there's more than one
way to
get the job done, and that there isn't just one "right"
way. BTW,
I haven't seen Jim weigh in on this yet.
Best Regards,
Charlie
On Sat, Jun 26, 2010 at 6:47 PM, Charlie Noah
<[email protected]
<mailto:[email protected]>> wrote:
Jim,
I agree that the OP needs more training, but why do
you have
a problem with the phone number being multivalued?
Charlie Noah
On 06-26-2010 2:38 PM, Jim Idle wrote:
You need to read the
knowledgebase but need more fundamental training. For a
start though, tell whoever told you that phone number
should be a multivalue field that they need some training
;-) Ask your supplier for some training options.
Jim
-----Original Message-----
From:[email protected]
<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of tanmoy
Sent: Saturday, June 26, 2010 6:41 AM
To: jBASE
Subject: how to create STUDENT database
Please tell me the steps to create STUDENT
database having
ID,STUDENT_NAME,STUDENT_PHONE_NUMBER(multivalue)
as fields
--
Please read the posting guidelines
at:http://groups.google.com/group/jBASE/web/Posting%20Guidelines
IMPORTANT: Type T24: at the start of the subject
line for questions specific to Globus/T24
To post, send email [email protected]
<mailto:[email protected]> To unsubscribe,
send email [email protected]
<mailto:[email protected]>
For more options, visit this group
athttp://groups.google.com/group/jBASE?hl=en
-- Please read the posting guidelines at:
http://groups.google.com/group/jBASE/web/Posting%20Guidelines
IMPORTANT: Type T24: at the start of the subject
line for
questions specific to Globus/T24
To post, send email to [email protected]
<mailto:[email protected]>
To unsubscribe, send email to
[email protected]
<mailto:[email protected]>
For more options, visit this group at
http://groups.google.com/group/jBASE?hl=en
-- Please read the posting guidelines at:
http://groups.google.com/group/jBASE/web/Posting%20Guidelines
IMPORTANT: Type T24: at the start of the subject line
for
questions specific to Globus/T24
To post, send email to [email protected]
<mailto:[email protected]>
To unsubscribe, send email to
[email protected]
<mailto:[email protected]>
For more options, visit this group at
http://groups.google.com/group/jBASE?hl=en
-- Please read the posting guidelines at:
http://groups.google.com/group/jBASE/web/Posting%20Guidelines
IMPORTANT: Type T24: at the start of the subject line for
questions specific to Globus/T24
To post, send email to [email protected]
<mailto:[email protected]>
To unsubscribe, send email to
[email protected]
<mailto:[email protected]>
For more options, visit this group at
http://groups.google.com/group/jBASE?hl=en
--
Please read the posting guidelines at:
http://groups.google.com/group/jBASE/web/Posting%20Guidelines
IMPORTANT: Type T24: at the start of the subject line for
questions specific to Globus/T24
To post, send email to [email protected]
To unsubscribe, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/jBASE?hl=en
--
Please read the posting guidelines at:
http://groups.google.com/group/jBASE/web/Posting%20Guidelines
IMPORTANT: Type T24: at the start of the subject line for
questions specific to Globus/T24
To post, send email to [email protected]
To unsubscribe, send email to [email protected]
For more options, visit this group at
http://groups.google.com/group/jBASE?hl=en
--
Please read the posting guidelines at: http://groups.google.com/group/jBASE/web/Posting%20Guidelines
IMPORTANT: Type T24: at the start of the subject line for questions specific to Globus/T24
To post, send email to [email protected]
To unsubscribe, send email to [email protected]
For more options, visit this group at http://groups.google.com/group/jBASE?hl=en
|
