de coupling data from logic techniques

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joshmillerAuthor Commented:
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
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.