Introduction to the Condes Address Model
In this article a data model is presented that supports the entry of addresses of all kinds, such as telephone numbers, street addresses, e-mail addresses, URLs, and every other type of address that people might invent in the future. He's worked on a generic solution that you can use with each customer who allows you to construct the data model. Here he'll show you how he gave structure to the data model, his classes, a handful of views, and the forms.
Figure 1 The four tables of the Condes Address Model. Note that theres no field for last name, et cetera.
Let me begin with a picture of the Condes Address Model (see Figure 1). There are only four tables involved! I'll start by explaining the way I name fieldsthe first character denotes the datatype, and the next three characters uniquely identify the table. This three-character section could be referred to as the table descriptor. Thus, any field name conflicts in views are effectively prevented, and you're also told what table a field in a view originated from. An underscore is used to ease the reading of the field descriptor on the right. Certain fields have special field descriptors. In the model, all primary keys have "PK" as the field descriptor. The field descriptor of a foreign key field is determined by the table descriptor of the linked table, followed by an underscore, followed by "FK." That's all for fields.
There's also a special rule for the naming of crosstables. In the example, the Rel_Adr_Whn table is a crosstable that links the tables Relation (people or companies), Address, and WhenToUse. Hopefully, you now understand the name of the Rel_Adr_Whn table. Although it's primarily a crosstable, it's also the right place to store certain information that's unique for a combination of linked records only.
In the data model, the crosstable Rel_Adr_Whn is the central place, rather than the Relation table. As a matter of fact, I've decided that it's best for the Address Model to even entirely exclude the Relation table! The information about the relation is restricted to the single field iRaw_Rel_FK in the crosstable, a situation that'll ease the coupling with eventual existing data structures.
At this point, it should be clear to you that, contrary to traditional data models, the Relation table itself is no longer the "obvious" place where one would store certain addresses of the relation. On the contrary, it's now obvious that it's not the place for addresses. If you want the relation's addresses, you need to take the relation's ID and select all records in the crosstable that have the same value in the related foreign key field. You'll also have to make a link from the crosstable into the Address table. And if you only want addresses of a certain type, you'll have to set a filter on the address type.
Examples of address types might be:
06 666 111 88
M. Emantsstraat 123 - 2011 HH Haarlem
Note that there aren't separate address types for private addresses vs. addresses that are connected to work. It was during my vacation in France that I had the time to think over this and other aspects, and I feel a need to tell you about those days...
It was in the tiny village Condes, in the French Jura, while camping on the shore of a beautiful lake on a beautiful day, that I took all the time in the world to think over a problem that I'd had with an earlier version of the Address Model.
A year before, I developed a model that contained a table "function" with records like "friend," "family," "account manager," "programmer," and so on. That table also contained a logical field "bizness." The idea was that a function is either bizness-related or not, so this field might then be used to distinguish between phone numbers that are used for work vs. those that are private. However, I had a lot of trouble implementing the model. While in theory it all sounded okay, it was a really big hassle to code in the real world. In those days, I also worked on a project with Andy Kramek and Marcia Akins. One day, I demonstrated the model to Andy, and he told me that others had tried similar models and had concluded that it's simply too complex to implement in the real world. Later, I came to the same conclusion, and ultimately I postponed the further development of the model.
But in Condes, I felt it was time to reconsider things. I tried to let new assumptions come into my mind. This requires you to think "lateral," as Eduardo De Bono said long ago. New assumptions will only come to mind if you're prepared to recognize your current assumptions and no longer take them for granted, no matter what advantage they gave you in the past. I felt it to be a breakthrough when I considered the idea that people, in search of an address, don't really search on "function," but rather have in mind what the relation communicated about the address. The idea is that the relation always communicates when to use the address! For example, John tells Martin to use phone number xxx when he's at work and phone number yyy when he's at home, and he wants people to use zzz when he's on vacation next week, and so forth.
The model that came to my mind didn't need a function table. Rather, it needed a WhenToUse table, and that's exactly what I've done. But the considerations also made it clear to me that addresses aren't always exclusively owned by a relation. Rather, the relation uses an address during a definitive or indefinitive period, and it may well be that other people also use that address, or have used it before, or will use it some day in the future. This aspect made me realize that it's better to leave out-of-use addresses intact and reuse them whenever another relation starts to use that address. It also became clear to me that whenever a relation confronts you with an address change, you need to check whether the change should also be applied to other relations who use the address. A good model must account for these aspects as well.
The new foundation for my Address Model was laid down, and in remembrance of that day, I decided to name the model after the village.
So, these contemplations should also explain the important place of the WhenToUse table in the Address Model. One might start with an empty table, but it's more likely that you want to start with certain entries and allow the user to add other entries. For example, I imagine that you add at least the entries "at work" and "at home" (read "when at work" and "when at home"). The data entry should ensure that each entry of this table can't be removed. Thus you can use at least the basic entries in your code, enabling you to print the addresses that are work-related (as an example) in reports and on screen. It's my experience that the number of records in this table will remain limited, even after many years. The rest of the model implies that records in this table will never become obsolete, for the model assumes you'll never want to physically delete outdated addresses.
On the other hand, you do need to instruct (and maybe even brainwash) the users of the model, for they'll have to understand how to "read" things. They have to be made aware of the underlying concept!
Another peculiarity is that the field cAdr_Address, which has the actual address, is of type character, no matter what type of address we're dealing with. Luckily, even multi-line addresses that the user enters with an edit box fit into this type of field. The ASCII codes 13+10 will be stored (and restored) nicely, together with the normal characters. A length of 60 will probably suffice. Such a length may look terribly long for a phone number, but why would we mind about that? Storage capacity is no longer our main concern, and speed is still okay, for we're not dealing with memo fields (which would give slower times indeed, I guess). And on the other hand, another feature of the model will save capacity.
That feature is the fact that any address should be stored only once, and all relations who share a certain address should be linked to the same primary key in the address table! This also implies that an obsolete address may come into use again. Of course, this has to be realized by the programmer.
At this point, you may be puzzled about the fact that I not only store street addresses in the Address table, but also phone numbers, URLs, and so forththings that people aren't directly seeing as addresses, but as entities of another kind. But I do think of them as addresses. In my opinion, an address is some code that lets you get in contact with a relation or, even more abstract, with an entity. It's information about where and how someone or something can be reached. This usage of the word "address" enabled me to simplify the model. Also, new types of addresses can be added without any effort of the programmer. Since the day I adopted this definition of an address, I got used to it very quickly, and now the concept is obvious and natural to me. I'm sure that users will also adopt it quickly and easily. But again, you'll need to instruct the users and make them aware of the underlying concept!
You may wonder about the relationship between the WhenToUse table and the Addresstype table. I want to make you aware of an interesting problem that the user may encounter. As should be clear at this point, on the one hand, the Addresstype table is used to distinguish between a telephone number and an e-mail address; on the other hand, the WhenToUse table lets us distinguish between a telephone number that should be used when the relation is at home, vs. the number that should be used when the relation is at work. But it may happen that the user will try to bypass the use of the WhenToUse table. The user might decide to create address types "Telephone at work" and "Telephone at home," for instance. I want to emphasize that this would not be good practice at all. You should try to prevent such abuse. Again, you'll need to instruct the users and make them aware of the underlying concept!
There's one more point about data entry that I'd like to emphasize. It's about the mindset of the user, or even about the procedures of the organization when it comes to entering new addresses and making changes to them. Traditionally, when entering a new address, we select the relation first. This procedure remains the same in my model. But how about changing a relation's address? Traditionally, in this situation too, we select the relation first. But in the Condes Address Model, there's no need to do that. As a matter of fact, it's even better not to do that! Rather than selecting a relation first, we simply type in the address and let the program search for all relations who use this address. Then we can offer the user the opportunity to change the address with regard to one, some, or all relations that were found! This principle should be implemented in the program by the programmer. But, still, I think it's wise to make the users aware of the underlying concept.
Implementing the model in the real world
It's nice to develop new theories and models, but they're worthless if they're not easy enough to implement in the real world. As I mentioned earlier, Andy's comment about my previous model was that similar models had proven to be a hassle to implement in real life. I realized that I needed to test my model in this respect. So I did...
I developed two databases, six views, two classlibs, and nine forms.
The main database contains the tables of the Condes Address Model. The second database contains a Relation table, for testing purposes.
The Condes database contains four views:
A view to retrieve the address types.
A view to retrieve the "when to use" descriptions.
A view to get zero or more addresses based on the ID of a relation, the short of an address type, and a "when to use" description. The latter two view parameters might contain wildcards.
A view to get zero or more IDs of relations based on an address and the short of an address type. Both view parameters might contain wildcards, and the search for the address is case-insensitive.
The Relation database contains two views:
A view that retrieves information about the relations who use a certain address. It's also an example of the way a Condes view can be linked to the Relation table.
A view that will get the name of a relation based on the relation's ID that's stored in the crosstable.
The main classes in the Condes classlib are:
A custom class that specializes in the data handling. It's the interface to the (read-only) views, and it also has a Save() method that's used to save a new address and a Thru() method that's used to outdate the address or modify the thru date of the address.
The Save() method will always create a new record in the crosstable, but it will only create a new record in the Address table if it was unable to locate a previously stored equal address in that table.
Note that an address change can be realized by first outdating the current entry with the Thru() method and then calling the Save() method, which creates a new entry. This procedure also ensures that we can retrieve obsolete addresses at any time.
A custom class and a container class that handles several "options" we're going to need to offer to the user. The user will want to set a filter to obsolete, current, and/or future addresses. And there's also an "automatic search" option.
A container class to enter a new address.
A container class to search for relations who use a certain address.
A container class to display addresses that are used by a certain relation.
A container class that enables the user to change an address for one, some, or all relations who use the old address.
A container class that enables the user to outdate an address or modify the thru date of an address for one, some, or all relations who use the address.
The only class in the other classlib is a custom class that specializes in the data handling of the Relation table. It's the interface to the read-only views. There's no Save() method, for changing the Relation table wasn't part of the tests that I did.
The forms are the hosts for the various containers and custom classes, as described here:
One form can be used to change the Addresstype table. It's really only a very simple grid control with its RecordSource property set to that table.
In the same vein, another form can be used to change the Relation table. This too is a very simple grid control with its RecordSource property set to that table. Remember that we're not testing the Relation table!
One form is the host of the container that lets the user enter a new address.
One form is the host of the container that lets the user change an address.
One form is the host of the container that lets the user outdate an address or modify the thru date of an address.
One form is the host of the container that lets the user search for those who use an address.
One form lets the user select a relation and then shows the addresses of that relation. Moreover, it provides push buttons that will call some of the aforementioned forms.
One form contains five tabs, each one hosting a previously mentioned container (see Figure 2). Thus, this form offers all of the functionality the user needs with regard to addresses.
The final form is an as-top-level form. It enabled me to build an executable that demonstrates the various forms.
Figure 2 The five-tab address form. Rightclicking on an address is enough to start drag-and-drop.
In the beginning, all of my attention was focused upon implementing the Address Model itself. It was the phase of development of the databases, the views, and the custom classes that specialize in data handling. Was it difficult? No!
Encouraged, I went on and developed the various containers and the forms that host a single container. Was it difficult? No!
Then I developed the form with the push buttons. Was it difficult? No!
Finally, I started on the form with the tabs. Was it difficult? Yes! But was that because of the Address Model? No. The difficulties that I encountered are the kinds of difficulties I encounter each time I use a multitude of grids on several tabs that depend on each other and therefore have to be refreshed under certain conditions.
My overall conclusion is that this Address Model, contrary to previous attempts, is easy to implement. Moreover, when you use my databases, views, classes, and forms, it may even prove to be peanuts. I hope.
You're free to try the Condes Address Model. Each class and form has a Documentation() method that contains hints on how to use the class or form. I'm sure you'll find ways to extend the functionality. For example, you might decide to add a field to the WhenToUse table, enabling you to offer the user either a restricted list or a full list, depending on the setting of a programmed option group. Another idea is to add a field to the Addresstype table that contains an input mask for that kind of address. And another field might be used to store the name of a validation routine for that specific kind of address type.
Think lateral, and you'll start wondering why on earth you stayed with the traditional model for so long.