Challenge
You’re starting with a new database, data warehouse, etc. and think you have a nifty way to bring in all the phone numbers, emails, and addresses you need to track for customers, employees, etc. or even better you inherited a system that no joke has pager_number_3 as a field and you internally start to weep.
Have no fear! We are bridging this gap once and for all. In this series I will begin at the conceptual model and drill into a physical deployment of all pieces of what Silverston has described in his books as “contact_mechanism”.
Approach
The first thing we must do is make sure pager_number_3 never makes it into another database (or any equivalent of this cringe worthy abomination). How you may ask? We will look to international standards, normalize, control with lookups, add temporality to records and lists, and hopefully get to a model that can handle any contact mechanism now or in the future.
Simply modeling the standard US or a single country solution will not be considered a win. I intend to do one post a week on this series until we have covered all aspects of the model. Along the way I invite you to provide feedback and will incorporate as appropriate in the model.
Contact Mechanism
Relation to Party
In this model contact mechanism is nothing more than a relationship table with an identifier stored for each of the 3 main types of contact mechanisms being Telecommunication Numbers, Electronic Addresses, and Postal Addresses. The reasoning for each of the names will be explained in additional posts. However, the intent is that only one of the Foreign Keys will have data in a record, this is acceptable as it allows us to build foreign keys that are not governed by a type field.
In our relation to party we bring in a role, this allows us to focus on the information of the contact mechanism in its respective table and the role that it plays in the implementation of a specific record in a system within another. For example 867-5309 could be a customers home number in one platform and a mobile number in another, or it could be phone_number_1 in one platform and phone_number_secondary in another.
Telecommunication Number
E.164 International Format
This is built out to what I would consider a logical level. In this view we have still not accounted for temporality or the possibility (and reality) of a standards change.
However this shows the intent of having information controlled by a standard, being able to be governed and monitored for quality, and to be utilized in applications/systems. By breaking out the country_code we can validate that any number in the system has a valid country code. The standards are a lot more lax for the national description code, and the subscriber number, but as the min length of a country code is 1 and the max numeric length of a number is 15 our only option is to make both a varchar (14). The last two fields are to format the number for use in applications. The reason for the split is that some numbers like those in London which has 020 as the national description code require the 0 when in the UK and the leading 0 is excluded when dialing the number from a different country
Type Code
The idea of the type code is to let us know what the use of the number is currently, as numbers are recycled this could require updates through time. Planned acceptable values are Mobile, Fax, etc. It should make no reference to the purpose it would serve for a specific party, but is applicable and important due to laws and regulations restricting use of certain types of phone numbers in specific business practices (example: when autodialers were no longer allowed to dial mobile numbers in the US)
Summary
Overall, we have been exposed to the approach for this type of model, have laid a foundation to extend the model, and understand the goal/purpose of the exercise. If you liked this post please let me know, if you hated it tell me too, if you disagree with anything I said call me out. Data is my passion and debating modeling is my idea of fun so lets start a conversation. Until next week.
No comments to show.
