de coupling data from logic techniques

Posted on 2004-11-18
Last Modified: 2010-04-17
Whoever decided this was a good way to store information about a person?
ID | Name | Address | Phone | Email

Cause day two they say, oh we need URL and cell phone and fax and home address.
Then some bright person decides ok we will have tables for generic locations and
phone with a phone type lookup table.

ID | Name   (nametable)
ID | Address | City | State | Zip | AddressTypeID  (addresstable)
ID | Phone | PhoneTypeID  (phone table)
ID | PhoneTypeName   (phone lookup types table)
ID | AddressTypeName  (address lookup types table)

Then someone says, ya but i need to store information about business's and their
employees contact information for work phone and work addresss needs to be tied
to the same address as the place they are employeed cause god forbib we actually
have duplicate information, and we also need usename and password info for the company
website and security level information for permissions to 4 different company websites.

and we want unparsed full legal name field
and we need 7 fields for parsed name cause our customers have legal titles before
and after their names
so then we have 15 tables for contact information...

OH! and we need 3 different software systems to be able to use the information and
they all have custom contact info unique to each system...

so now we have 25 tables, and a seperate software department for data translation
and compatibility issues

what about 1 table?

EntityID | AttributeName | Value | DataTypeDescription

1 | Name | Josh | string
1 | HomePhone | 23456778| string
1 | WorkPhone | 1123234234 | string
1 | BritishRoyaltyTitle | Duke | string
2 | FullName | Joe Smith PHD| string
2 | EmployerID | 999 | integer

1) Completly backward compatible with any system that knows how to read 1 simple
table, systems can ignore all attributes that they are not familiar with, and data
can be stored twice if needed to be fully legacy compatible with and old system.

2) No nulls anywhere

3) Rules about wether a person must have an EmployerID to be allowed in the system
can be implemented by a logic based routine when we have the time to build it.  
Instead of trying to use referencial integrity to support such logic which it was
never intended for.

4) extendable for plug-in software type architechture.

 so my question is...

is there any book in the world that recommends to to store contact info this way?
i have never seen one that even hints at it

does anyone ever discus what the real purpose of referential integrity is, or datatyping
and what its drawbacks are?, and why it has nothing to do with system business logic
and why it should not be used for that?

have you ever seen anyone store any kind of contact information this way?
Question by:joshmiller
    LVL 3

    Accepted Solution

    There is something compelling about the god-table idea, but I think it's likely that it has as many flaws as the system you want to replate.

    For instance:

    #2 - No nulls.  This isn't true.  If you ask for every individual's First and Last Name attributes, you're going to get some individuals with one or both of the attributes missing.  Those are your nulls and you're going to have to deal with them.

    #3 - You are solving the ID assignment problem by assiging individuals ID's automatically.  These are the 1 and 2 in the first column of the god-table.  This may be a useful idea for your current database implementation.

    But the most profound problem is that by moving the type and relationship information out of the database structure and into the application logic you're assuming a big responsibility (think maintenance) and disabling a lot of things that databases can do for you (think performance).

    From a software project management perspective I'd say that you need to contain the database issues in such a way that developers aren't exposed to the issues around the particular data representation you have.  Define an API that represents the data operations applications need to perform.  Use stored procedures to do all queries and updates.  Then let the database administrators do whatever is necessary to meet the contract of that API.  And if it turns out that a god-table is the right way to go, they'll be able to restructure the datastore and you'll be shielded from the changes by the API.

    That said...

    You may find that XML databases are aligned with this idea.  If I wanted to have an entity with a set of attributes that I could store and query on, I would look in that area first.

    Author Comment

    thanks for the reply

    i was thinking, no data would exist for a name attribute if it is null
    like one person could have just a full unparsed name, and another person could have 35 different attributes
    if a persons date of birth was never entered, then there is no row for that attribute at all.

    you are right about the god table comment, but i was thinking that other data patterns, such as money transactions and log files would definately have other tables. but still in a generic sense of functionality only , and no named columns that have any relation to user interface terminology.

    the projects i build are all for small business's or large business's where only a small department is using it, so i dont get thousands of queries per second, more like a handfull, so i have the luxury of having very little issues with database server performance.

    on the other hand, it seems they are calling me weekly to add new fields of information they would like to track, and its work to change the database and the levels inbetween when i could use the attribute system.

    i saw the method that is in for .addattribute for HTML, since html tags can have the bare minimum attributes, and an unlimited number of made up ones that would be ignored by the browser.
    but other programs or browsers or whatever, could use those new tags.  in fact your code can read from them later on.
    i really like that method of late bound data storage.

    i wish there was more articles on the exact nature of referencial integrity and what its really needed for in our world.  obviously saftey critical systems, but what about systems that are going through constant change and suffer from constant structural change.  

    anyway thanks for the input, ill be sure to award the points
    LVL 3

    Expert Comment

    Yeah, I figured you weren't *storing* missing attributes, but my point is that when you ask for all individuals' first and last names, some will be missing and you'll have to handle that case.  Those are the same cases where you deal with NULLs right now.  I suspect that you aren't concerned about storing NULLs, just handling them when you see them.  And the god-table doesn't help you escape them.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
    This is about my first experience with programming Arduino.
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now