Scope
Last time we added the base model, this time we have our first refactor and the first additional buildout. Compared to last weeks changes, the modeling enhancements could be viewed as limited or elegant depending on the perspective. In this post we will evaluate:
- Why do we refactor models?
- Why is the only field added to Electronic Address a plain text field?
- You said in the first post you would cover naming, are you going to talk about that here?
- Why are there all these lookup tables?
Why do we refactor models?
What changed?
The change from last week is nothing more than removing (and renaming) the value for the country code from the telecom number table.
Why change?
This was an important change as it allows us to not hardcode in a value when we are looking for records, allows a smaller amount of data to be updated on a change, should be a slowly changing dimension in the table, and is controlled by a standard.
Putting in the telecom standard lookup table allows is to know what standard this came from. The table could be enhanced further if needed to track the name of the standard and year, but as the E.164 did not seem to be year specific it seems that a code field is more than sufficient to store the information on the standard, with the description providing a place to give an overview of what the standards encompass.
Bringing this together in the telecom country code table we are able to see that now we have the value with a surrogate identifier, and a foreign key to an identifier specifying the standard. This allows us to know where the value came from, if the current standard updates the value we simply have to update the value, if a new standard comes out and the value isn’t changed we just update the identifier. As mentioned this may seem minute but this flexibility is a key design pattern when working with metadata.
Only adding a plain text field to Electronic Address
At first you may be thinking well yea plain text fields solve all our problems, nice shortcut. In reality this single field really is our best option.
Wait you could have made a email table and website table
This is true! However we want to remain flexible for the future as I can right now say, what about:
- Twitter Handle
- Facebook Page
- etc.
By providing the plain text field (since all of the previously mentioned addresses are a 1 block field) we allow the flexibility to store whatever may come about. The type however allows us to know what is in the field and apply the appropriate Data Quality checks. So while at first this seems like a shortcut we must remember that this is accounting for a immature and continuously evolving real of information.
Naming within our model
We are going to ignore party since the primary focus of this post is the contact mechanism.
Contact Mechanism
Contact mechanism always makes people go huh? Until you ask well if i have a person or a company that I need to select a blank to contact them what could we use for blank? You will most likely hear things like methodology which is longer than mechanism to start with, and implies a method not a means. It is difficult to come up with a better universal term for this.
Telecom Number
We could have said phone number, but this doesn’t include things like faxes, pagers (I swear these still exists in systems, any application engineers out there please stop making this a thing), and other things that could be splitting hairs like number forwarding that are not true phone numbers. Telecom (telecommunication) Number allows us to refer to all of these things as it is really designating the numeric reference to the type of communication.
Electronic Address
Covered above. This is moving too fast to really call anything else, as it is really a catch all for anything other than a postal address and telecom number.
Postal Address
This was selected as physical address excludes things such as PO boxes, mailing could be anything, the customer could have multiple addresses, and you never know what interesting terminology someone will introduce to reference these. But at the end of the day you can receive mail at any address, thus Postal Address.
Lookup Tables
Lookup tables are an important part of Metadata Management. Without lookup tables we would not know what fields are reference data, which standards are followed, if the reason a value on a record changed was from a reference data update, or even if the value is valid. Therefore while it may appear overkill at times, these tables provide important functionality to the database.
Summary
This post wasn’t as content rich as the last, but does provide an overview of a lot of foundational pieces that we will need moving forward, next week we will move into postal address and see how far we get. As always please feel free to comment and let me know what you’re thinking!
No comments to show.
