>I would like to begin using memo fields to store data normally held in child tables, mainly because of their flexibility. These memo fields will be used mainly for contact information (phone numbers, email address, web site, physical addresses, etc.). Has anyone created functions or classes to append, retrieve for edit or remove specific records from the memo fields?
Yes, VFP team did :). Alines and GetWordNum() are your friends. Split the memo into array by aLines(), and then split the line at the = sign by the other. The first word is your property name, the other is its value.
BUT: that value is always a string; if you need a number, date, time, a logical or anything else, it isn't easy anymore, and you are halfway into inventing your own XML format. Also, retrieving such data is decent processor work - no matter whether you'll use alines() for every value you ever access (and if you need four values from the same record, run it four times), or once per record (and run the code to locate the line in the array and extract value from it for all values in the memo, even when you need only one). Still, processor time is cheap nowadays, and specially if your data are sparse (there's a property that only 2% of the records may have - and about half of them are like that), your approach may be well worth it.
> I believe each record should be pre-pended with a “Type” (“FAX, Business”, “Voice, Direct”, “Voice, Residence”, “Address, Mailing”, “Email”, etc) key value for searching and retrieval.
If it's about phone numbers, think also about storing them separately from the owner, because multiple people may use same phones. For example, in some company you have four contacts sitting in the same office, maybe at different times. And they share the fax. Would you have the fax number and office line number four times? Also, the priority of the phone - a telecommuter will be found on landline first, cell next, fax maybe. Those four guys on office line, then their cells. A travelling guy may be on any of his cell phones (one for the US, another for Europe), but if everything else fails, try office. So ideally, phone numbers and types in one table, people in another, and a person-phone link with priority (and maybe expiry date or at least a status, so if he's on a long trip you disable his office phone), so you can link them m:n. This may be too heavy if your app doesn't need that much granularity - but if it has a potential to hold hundreds of numbers, this sort of layout may come handy.
