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?

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

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

How about "Communication"?

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!

InfoCard or simpler Info ?

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.

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.

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 .

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

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.
  

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.

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.

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.

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'.

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.

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.

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, 200718 yr by Guest

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.

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 )

Don't forget SeedCode Contacts!

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.

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.

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."

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.

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.

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

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.

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.

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

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

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

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.

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?

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.

http://www.dbdebunk.com/page/page/3161496.htm

I'm confused...

Are you claiming to be the enlightened intellectual and that we are the ignorant morons who refuse to listen to reason?

And for how long must it be proven so again and again

Apparently you have never studied law.. or even formally debated. For you to be right and us to be ignorant you first have to rebut our arguments with reasoned explanations as opposed to telling us that we're wrong and dismissing us for our apparent extreme lack of intelligence.

No one here as far as i can tell is arguing against normalization as a concept. They are arguing against the need for enterprise level normalization that deals with terrabytes of data in a small business's organization who's data in most cases won't surpass a GB in it's useful life span.

What you seem to be arguing however is that there is one structure concept that is applicable to all problems and I put it to you that if this were the case our jobs wouldn't exist. A client could just plug and play 1 or 2 appropriate modules and that would be it.

If the topic is of such interest to you (and this is a serious question) why don't you get a job in normalization consulting at an enterprise level?

Alright, lighten up folks! It's only relational design theory.

teka,

You had asked about these sort of design choices some time ago:

http://fmforums.com/forum/showtopic.php?tid/185942/

but it seems now that you’re hearing some opinions, you don’t like what you hear. Hey, we’re just trying to suggest a practical limitation to normalization. Give us a break! :

Anyway, earlier you made some assertions and posed some questions about my position, so I’ll try to oblige.

Nothing personally, Ender, you usually have good points, but you're wrong on this...

What, me wrong? Say it ain’t so!

you make an assertion here but not really supported...This is a sore point for me as I always see 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.

Yeah, I know what you mean about unsupported assertions. I hate those too. My only excuse is being pretty busy today. I guess I thought it was clear enough given the abstractness of your examples. But it looks like comment and Genx have filled in about this, so I’ll reserve further comments unless you have a specific structure you wish to discuss.

As for “One table wonders”, I hope you give me more credit than to think I’d recommend or use a flat structure. C’mon, man! I’ve been weaving relationship webs for years. Check my post history if you don’t believe me.

Anyway, what’s this business about corruption? A database doesn’t get corrupt just because it’s flat. On the contrary, in a normalized system, there are more keys and cascading delete things to watch out for (not corruption, but it might appear so to the user if these are out of place).

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).

Hey watch what you’re calling fallacious! : Maybe you like to spend a lot of effort trying to hide tangled innards in a pretty skin, but some of us don’t like to dink around with all that. While the interface need not depend on the model, it sure makes it easier to go with it than to fight it.

..this indicates to me an all-too prevalent confusion of the Logical versus Physical model.

Although I’m trying to talk about the ERD here (not the relationship graph), it’s reasonable to consider the implications of the ERD in what the relationship graph will look like. I don’t find this confusing.

Consider a solution with Salespeople, Invoices, and Contacts. In the "normalized" world, you might consider the Salespeople and Contact to both be sub-classes of People, and therefore design a model that shows this, but unless you intend to build the database with only one People table, this is not a useful abstraction. I personally don’t like the idea of overloading tables with different entity-roles, so I wouldn’t build a solution this way, nor would I build the ERD to show it that way. I’d keep them as separate entities.

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?

Well, if we’re talking about the Address example, I like Fenton’s idea for a general purpose Contact database, where phone numbers and addresses come up in different places for different purposes, but I can’t generalize that for every situation.

In one place you may only need to record someone’s home address, in another case it may be important to have a dedicated shipping address and a dedicated billing address, or as in a Contact database, it may be important to have flexibility and allow a variety or address types.

I know what you’re thinking. “Ender must have flipped his lid!” or “He must not be as smart as he thinks he is,” or maybe something offensive. :

So why would they not reside in a single entity? For simplicity. You may tell me that the same address could be a Contact’s home address and could be an Invoice’s Ship To address or Bill To address and could be an Employee’s home address, and if that’s your situation, then a single Contact with related Address may work best. But I don’t think that’s typical. More likely, the only address needed for an employee is a single home address (and possibly an emergency contact). Similarly, the scope of a Contact database may be restricted to a different subset of people (maybe totally independent of the Invoice’s contacts). For example, the people in an executive’s Contact database may not have anything to do with the peons that actually buy widgets from the company on a daily basis.

Sure the scopes of these could be limited by filtering the relationships, but in my view there’s not really an advantage to doing so, over using local fields or separate related tables. I don’t mind having a table for Contact addresses, and then keeping the Employee’s address as a set of fields. This may grate against the normalized view of the universe, but it simplifies the implementation and gives some reassurance about security (no chance of the executive’s friends getting sent an Employee Termination letter because of some script mishap).

Don’t get me wrong, if there’s some useful purpose to having things combined, then it’s worth doing. I’d see this for example, if letters need to be sent from a combined list of different groups of people.

I still don’t get the “Corruption” argument, but I think others have already addressed the rest.

If you can clarify without getting your undies in a bunch, I'd appreciate it. :

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

Aaack! Has Soren brainwashed you, Stephen?? STEP AWAY FROM THE LIGHT...COME BACK TO US, STEPHEN.... :shocked:

LOLOLOL!!

:jester:

Although ... I doubt Stephen could be brainwashed.

http://www.dbdebunk.com/page/page/3161496.htm

Hey teka, what's with the reply?

If you've got a myth to debunk, let's hear it (use small words for us uneducated folk). But don't go calling David J. "vociferous". What's up with that?

Interesting thread for such a simple question.

Teka,

If you just wanted to dismiss peoples ideas and thoughts, why participate in the thread?

Michael

Edited June 6, 200718 yr by Guest

It is one thing to have an enlightened discussion of issues where people are open-minded and try to learn, I am always willing to work toward this goal.

One thing I will not do is engage in unproductive argument against arcane arbitrary departures from proven foundation knowledge, that based on many of the proposed ideas in this thread, have no proven basis other than anecdotal, and are so vociferous in their ignorance that they may also lack the ability to recognize that they in fact might be quite ignorant of the fundamentals.

Now, there are two possible replies to someone questioning the basis of your work, one can either attack them, as in "Who the hell are you to question us, and how many doctoral degrees do you have, etc..." or one may engage in some introspection to determine if their knowledge might indeed be deficient. If such a person were to ask me where they might review their knowledge of the fundamentals of data management I would be only too happy to oblige with a reference list.

and are so vociferous in their ignorance that they may also lack the ability to recognize that they in fact might be quite ignorant of the fundamentals.

It's comments such as the above that turn a simple discusion into an "unproductive argument".

Just my 2 cents from what I read.

:backtotopic: