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
