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?