Another word for "Phone Number"?

[David Jondreau]

I can't think of a good term to use for Contact Methods (which is pretty weak) that encompasses phone numbers and e-mail addresses. I want to name my Phone Numbers table something else, as well as label a portal appropriately.

Any ideas for a term that covers e-mail, fax numbers, cell phone numbers, home phone numbers, pagers, etc?

[Søren Dyhr]

Ruling out semaphor, morse-code, tourches bonfires etc???

Calling a table something daft is forgiven, just make a popup for the attribute ...such as:

http://www.filemakermagazine.com/videos/data-tagging-classification-vs-organization.html

--sd

[bcooney]

Contact Info? TeleCom? How I get a hold of this guy?

[mr_vodka]

How about "Communication"?

[David Jondreau]

I'm adding a CRM module to this solution and I'm going to have a table named Contact Points (a term I'm not excited about) to log customer-client communications. "Communications" is too broad for my original question, but is now an alternative to "Contact Points".

Contact Info is close, but this table won't include addresses, which also qualify as Contact Info. Maybe I should just include USPS info!

No Soren, no smoke signals, but I forgot IM in my original post. Keep the suggestions coming!

[Raybaudi]

InfoCard or simpler Info ?

[Genx]

I use:

Contact Info

Then have:

Type

Content

Address is address.. don't be so picky, your users will forgive you if you explain it to them, or if not maybe they'll think of something better... In the mean time get on with the project and they'll like you more if you finish it quicker.

[teka]

This really belongs under Database models, not in right brain, but why quibble, this whole site is organized like Mom's attic. -)

Many thought leaders in Data modeling (David Hay, Len Silverston, Graeme Simsion) have already developed logical abstraction models for a Supertype of phone number, which is called a "Contact Mechanism" for a party. There is usually a Contact_Mechanism_Type entity, which in FileMaker terms (PHYSICAL MODEL) would be a table serving as a value list, in data modeling terms a value list is called a domain.

Other contact mechanism types are web address, email address, fax number, physical address, postal address, etc. There is usually also a relation to a Contact_Mechanism_Purpose entity. If someone is interested I can put up an image of the model.

[Genx]

this whole site is organized like Mom's attic.

Your mom's attic is farily clean I take it?

We do try... anyway:

Web, Email, Fax, etc. i can see shoving in one table, addresses? You're going to have to get creative to work those into the same structure, but learning is always good so chuck up the image .

[Lee Smith]

This really belongs under Database models, not in right brain, but why quibble, this whole site is organized like Mom's attic. -)

Hey, we aim to please.

Only problem with your suggested topic is, we don't have one called Database models, but we do have Relational Database Theory, and Define Fields.

Lee

[teka]

Well, some say it depends on what the Address (contact mechanism) is about, the semantics that is, if it is a physical or facility LOCATION then that is usually expressed as a GeoCode Location, (Geospatial Entity Object Code, a hashcode for a precise longitude, latitude, altitude and other info required to locate a point on the earth) which allows GIS map positioning, and a relation to a Facility entity. So then the purpose would be GEOcode. A GEOcode can have a relation to a postal address, if the GEOcode location receives mail, see diagram parties 2.8 in a follow-up post. The models are from Len Silverston, "The Data Model Resource Book, Vol 1", highly recommended.

Other purposes could be:

• Postal address, as a building (for example one that takes up a whole block) might have several street addresses but the post office might deliver mail to only one. That is also a relation to the Facility.
  
• Freight Address, where there might be a loading dock or freight receiving area that could be defined with a purpose Freight Shipping and/or Freight Receiving.
  
• Visitor Address, there might be a Reception or Visitor Entrance address purpose.
  
• Employee Address, an employee entrance address purpose
  
• Emergency Response Address, which could be the address given by a 911 call.
  

[teka]

Thanks Lee, yes theory would be the WRONG place. I guess the precise term would be Logical Data Model Discussions.

Here are the additional diagrams for Postal Address Info and Party Contact Number.

[Ender]

Oooh pretty pictures...

I'd submit, however, that there is a balance between capturing the minutia of a thing, and usability. Breaking addresses or contact types into so many related parts seems overkill to me unless it is important to have that level of detail (and the users are willing to accept a more complex interface). I'd venture for most people's needs, it's better to have a simpler design with fewer relationships to jump through to get what they want.

[teka]

I'd submit, however, that there is a balance between capturing the minutia of a thing, and usability. Breaking addresses or contact types into so many related parts seems overkill to me unless it is important to have that level of detail.

Nothing personally, Ender, you usually have good points, but you're wrong on this...you make an assertion here but not really supported...This is a sore point for me as I always see FM Developers making "One table wonders", as in you wonder how it keeps data from getting corrupted, and you wonder why they are using a relational database.

(and the users are willing to accept a more complex interface).

This is a fallacious argument, tying interface to data structure. There is nothing inherent about a relational structure REQUIRING a complex interface, but this is implied (false dilemma)...this indicates to me an all-too prevalent confusion of the Logical versus Physical model.

I'd venture for most people's needs, it's better to have a simpler design with fewer relationships to jump through to get what they want.

Simpler design? Normalized to what level? 1NF? 2NF? 3NF? Which relations would you denormalize? For what purpose? And what would the possible data corruption issues become then?

Would you propose to have the Company contain address (1NF), so that if the company record were deleted then the building would be vaporized? (A deletion anomaly) Or perhaps what if it were a new building, and the company had not moved there yet? Would you propose to create a company record with no company name (NULL) in it to hold the new address? If so, how can you guarantee that when a company would move to the address they wouldn't re-insert the same record (an insertion anomaly). Wouldn't all the code to do this be considered complex? So the correct tradeoff is "simplify or denormalize the logical data model to the peril of additional complex code to script all the additions, changes, and deletions, accept nulls, and possible redundant or duplicate records, and make the application dependent on the data structure. Then if the needs change, and you have to add an "Address 2" field, all the code must change as well.

Or even if you had a relation to Address (2NF), what about multiple companies sharing the same building? If you delete a company do you delete all their addresses, and what happens to addresses shared with other companies? (another deletion anomaly)

FileMaker's Relational Capabilities should not be so easily circumvented or dismissed as irrelevant, the Relational Model of Data is the only data model proven mathematically to not corrupt data, and was the reason the Relational Model was adopted in the first place.

[Fenton]

I tend to think of contact info as either electronic or addresses; so those are the 2 subtables. Within these I have subgroups.

Electronic comm table:

Phone#'s; with type (home, work, cell, etc.), primary flag

Faxes

Email

Web

Addresses:

Home

Shipping

2 tables, but each subgroup is presented as its own portal. That way its subgroup is automatically set into a field, by using a constant text field in the portal's relationship. Example:

z_cPhone_txt, calculation field, unstored = "Phone"

related to subgroup, allow creation

or, another example,

z_cEmail_txt, calculation field, unstored = "Email"

The separate portals allows each different subgroup of data to be in its own labelled portal on the layout; its own size (email is wider than phone#'s), with its own "types" value list, if appropriate (like no "cell" choice for Email, but "home" or "work").

Any further processing of the data can use the subgroup field internally. Like 'Find all emails'.

[Ocean West]

however with this approach of using "Phone" or "Email" can cause extra data to be stored and in every record if your data set is VERY large it is a waste of space and can impact performance. Instead of using constant "text" fields to break up your data by type, on your table you can just use a new key.... for each data type.

for example

In my "Details" table that stores addresses and telecom data (email/web/phone/fax)

for each data type that I wish to access by type just include a new key in the details table.

[teka]

Portals? I like to stick with Kachel's advice to almost never use the native FM portals for adding records since they are so weird.

That seems like an arbitrary breakdown to me, why wouldn't phone and fax numbers be considered alike, as they all use the public switched telephone network, so it could be subtyped from the SuperType Contact Mechanism as PSTN Contact Mechanism. But there is no reason in the PHYSICAL FileMaker implementation that you couldn't implement this with separate table occurrences tied to the same table specifying a different subtype.

[teka]

Again logical vs physical...

The attributes of the entity need not be stored in the table itself as a physical artifact. The reason for subtyping in the Logical Model is that different subtypes may have different unique attributes, as well as some common ones that are inherited through the SuperType.

Soren quoted a link to Petrowski's site data-tagging-classification-vs-organization showing how in FM6 that a physical implementation of a subtype might have separate and variable numbers of attribute records, and thus no nulls. But FileMaker does not store nulls anyway, so what's the beef?

Edited June 5, 2007 by Guest

[Ocean West]

Also nothing prevents you from using multi-predicate equijoins relationships between your contact table and your detail table as in the example here...

Then you can if need be find ALL detail regardless of type by contact/company...

if you notice I have the same field on the left side but populating 2 fields on the right side.

[Ocean West]

Any ideas for a term that covers e-mail, fax numbers, cell phone numbers, home phone numbers, pagers, etc?

David you may wish to check this out...

https://www.productive.cc/pci_cart/FMPro?-DB=PCSC_Products.FP5&-Format=catalog.htm&-Lay=CGI&ID_No=54&-Find

(FMPug.com members get a discount )

[teka]

Don't forget SeedCode Contacts!

[Fenton]

You did see that I said "unstored" for the calculation, so it would not add bloat to the parent table, though it would add some to the child table.

But, you know, I'm thinking now, why not just put phones# in their own table, emails in their own, and web addresses in their own? Faxes are kind of a toss up. The basic idea is that you never do an operation (mass emails being about the only one I can think of) which uses 2 different "types" of contact info at once.

As far as never using portals, well, that's pretty limiting. I usually script adding to portals that are sorted, or very long, but for phone#s this is seldom an issue.

[comment]

Would you propose to have the Company contain address (1NF), so that if the company record were deleted then the building would be vaporized?

I believe in most cases I would answer yes, at least to the second part. As long as the purpose of my solution is to track companies - or rather SOME companies - I couldn't care less what happens to an address where no one of any interest to me resides. Or would you propose we all keep track of every address in the universe?

I don't think there's one 'correct' model that will fit every application.

[teka]

There may be more than one correct answer, but there are lots of incorrect solutions that the customer will end up paying for one way or another.

I still have not heard any proven justification for abandoning using the Relational Model. That is what you are arguing for is it not?

H.L Mencken said it better that I could in 1917:

"There is always an easy solution to every human problem--neat, plausible, and wrong."

[comment]

I haven't seen anyone suggesting abandoning the relational model, hence there's no need for justification. I did see PLENTY of valid justifications for de-normalizing a solution.

[teka]

Pardon me, but it's not called de-normalization unless you fully normalize the model in the first place. Then it's simply called an arbitrary data model. If that is what is being argued then that's fine with me, you cannot then assert that everything is hunky-dorey and there will not be any issues. That is why normalization precedes de-normalization, so everyone can know fully the consequences of allowing anomalies and what will need to be done in scripts to compensate, check, and correct for them. But Codd, Fagin, and Date, et al proved mathematically that there WILL be anomalies.

[teka]

For more reading on data modeling and why it is anot as intuitive as folks suggest, see:

http://www.dmreview.com/article_sub.cfm?articleId=1082215

[comment]

it's not called de-normalization unless you fully normalize the model in the first place.

I don't think I agree with that, but I don't want to get into semantics. My point is that - if I understood you correctly - you took it for granted that if several contacts have the same address, then that address is going into a single related record, instead of being replicated in each contact record.

I believe this is far from being taken for granted - in fact, such approach would be wrong in many, if not most, solutions. It may appear to be correct from the point-of-view of relational database theory, but it would be wrong from the point-of-view of system analysis.

Unless the fact that Acme Inc. and Widgets Ltd. reside in the same building is somehow SIGNIFICANT to the purpose of the solution, it should be treated as a mere coincidence. Same as John Smith and Anna Smith sharing the same last name, which would be rightly ignored in a business contact management solution - although it could be VERY significant if the solution was about tracking family ties, or criminal offenders.

[Genx]

Well technically you could normalize this solution endlessly, but as Michael points out it would likely be useless..

Take for example the address.

It consists of a country, a state, a city, a road name and a street Number.

If you want to completely normalize that then you need a table for country, a table for state which stores a country fk, a table for city which stores a state fk, a table for roads that stores a city fk and then a table for addresses that stores the street number and the road fk.

And that's great... really, but what use is it to know that three of your clients have a postal address in the city center?

Your "client" will end up paying more for the time that you waste on normalizing a structure to a level that is frankly unnecessary.

[teka]

Well that may be normalization in some universe, just not this one...

[teka]

Do you start from a blank file for every solution then? I truly believe that some level of practice must be absolute. You can't play high school ball against the Lakers, but I really think you need to go and be sure you know what normalization is. Seems from the context of your post that you don't. I hope that the client doesn't have to ask if the solution will break if they try to add an out of state customer...

[Ocean West]

[spoilerq:1] Le mieux est l'ennemi du bien.[/spoilerq]

[spoilera:1]The original quote in French is "Le mieux est l'ennemi du bien.", from Voltaire's Dictionnaire Philosophique (1764) Literally translated as "The best is the enemy of good.", but is more commonly cited as "The perfect is the enemy of the good."

In other words, pursuing the "best" solution may end up doing less actual good than accepting a solution that, while not perfect, is effective. One could also infer that the best makes that which is good seem to be worth less than it is.[/spoilera]

[Genx]

Normalization is the process of designing database tables to ensure that the fields in each table do not repeat, are fully identified by a unique KEY, and are not dependent on any non-key ATTRIBUTEs.

A technique to eliminate data redundancy.

The process of analyzing data to create the most efficient database structure, one free of redundancy and supprtive of data integrity.

A process for structuring data to organize it into its most natural, stable, subject-oriented, shareable, and non-redundant form.

I hope that the client doesn't have to ask if the solution will break if they try to add an out of state customer...

Hence why I would never structure data in such a non-nonsensical way -- yet it is still normalization, the elimination or redundant repeating data.

Do you start from a blank file for every solution then?

Every clients needs are different, so in most cases yes.

[Genx]

but I really think you need to go and be sure you know what normalization is

.

Out of curiosity, which authority are you a certified normalization expert under?

[comment]

Well that may be normalization in some universe, just not this one...

I am not sure what's the point of your remark, or how it relates to what I said. I never claimed that was normalization. On the contrary, I said that de-normalization is often called for.

I hope that the client doesn't have to ask if the solution will break if they try to add an out of state customer...

Please keep on the subject, and refrain from cheap shots.