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