Home Forums Database Design Relational Theory A simple example which, after 15 years of relational design work, still breaks my brain - phone numbers RE: A simple example which, after 15 years of relational design work, still breaks my brain - phone numbers

  • I would go for the normalized form, whenever possible (it depends applies but it's for exceptions).
    Integrity is my main concern plus it's easily extendable without much maintenance overhead, take less storage space, devs don't have to deal with null values (add overhead in the application layer), don't have to deal with empty string - default empty phone number (000-0000 for instance) and null are equaling the absence of, easier to maintain, and more than often a database is a "shared unit" queried from multiple sources. So modifying schema afterward is often very risky. More gain than loss for the small additional time to write at first.

    Of course the solutions with "contacts" only is easier to query for devs. In those situations (having devs difficulty writing t-sql), an "external interface" view (or sp) will do the required joins for easy querying while preserving the underlying schema safe.

    Also another point I feel important is what's the smallest indivisible unit a data can go before being unneeded by the business. What the phone numbers represent for the business. Is there any business value to know it's a landline over a cell phone or an IP phone? Does it bring more values distinguishing what type of phone it is? A phone number for a carrier is far more different from a business perspective than for another company that need to collect a phone number whatever it is because it needs to and won't do anything with it later.

    my 2 cents