JimI did point out that my approach wasn't the "correct" approach, but it works well for us.
We use this technique quite a lot in our system, and it allows easy extensibility. I guess if you wanted it to be "pure" you would have an associated attribute with the descriptions in eg :
<1> Home]Work]Mobile <2> 01142111111]08456862300]0784546564654 All we do is keep the contents of <1> in a seperate item stored once.I guess the fact that MV doesn't enforce a rigid database structure is good, but also a noose to hang yourself with... Maybe I'm due for hanging <G>
Jim - I missed my Wimbledon ticket - did you send it to the right address?? Simon =========================== Simon Verona Director Dealer Management Services Ltd email: [email protected] tel: 0845 686 2300 On 01/07/2010 14:04, Jim Idle wrote:
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.JimI 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 challengeanyone 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.DanDan, 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, CharlieOn 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 tanmoySent: 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%20GuidelinesIMPORTANT: Type T24: at the start of the subject line for questions specific to Globus/T24To 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%20GuidelinesIMPORTANT: Type T24: at the start of the subject line for questions specific to Globus/T24To 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%20GuidelinesIMPORTANT: Type T24: at the start of the subject line for questions specific to Globus/T24To 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
<<attachment: simon.vcf>>
