Wednesday, December 28, 2005

EAV nightmare

My charitable Christmas mood only goes so far. I'm looking through a spec which reads like a "database design nightmare!" theme advent calendar. Each page reveals a potential disaster more frightening than the one before. This is my favourite new year hang over inducing cocktail of entity attribute values and generic application design all wrapped up in a gloriously mal-specified mess.
Table: Parameters

id  VARCHAR2(50) PRIMARY KEY  --The application requesting the value
identifier NUMBER       PRIMARY KEY  --The name of the parameter
type  CHAR(1) --‘I’, ‘S’ or ‘B’ is type of value for the parameter
string_val VARCHAR2(50) --‘Y’ or ‘N’ or NULL if BOOLEAN or the string 
integer_val NUMBER       -- The integer value

note: The id holds a value comprising the IP address, type of
application and the instance at that IP.

4 comments:

William Robertson said...

I liked "integer_val NUMBER".

And what is it about VARCHAR2(1) that people don't like, so they feel compelled to make it CHAR(1)? I keep seeing that. Weird.

When a primary key consists of two columns, "ID" and "IDENTIFIER", you know you are in trouble. Then when you see that "ID" will actually be a concatenation of an IP address, a type indicator and some other damn thing, you know you are dealing with people who have no idea what database is. How on Earth do they get the job?

Tony Andrews said...

> And what is it about VARCHAR2(1) that people don't like, so they feel compelled to make it CHAR(1)?

When I was an Oracle Consultant we had a consultant's handbook of standards, one of which perpetuated the myth that CHAR(1) requires one less byte of space than VARCHAR2(1), and so stipulated CHAR(1) for all single character non-null columns. No doubt some of us spread the word...

Noons said...

string_val looks like a good one as well:
"y, n or null if boolean, or the string"? These guys need a datatype of "ANYTHING"...

Adrian said...

Nope, they need to model their data correctly and not resort to EAV.