The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

A lookup table nightmare

I have to deal with a lookup table that has 4 key cols named key1,key2,key3,key4 and 4 value cols named val1,val2,val3,val4. Primary/Unique key is on the key columns.
I am now being forced to have 4 keys for every piece of data I have to store in that table. If my object is made up of a key-value pair,I have been asked to insert a 'space' character in the other 3 key columns.

I don't like it for 2 reasons.

1. inserting spaces on key columns doesn't seem right
2. the team lead expects every small object to be translated to fit this generic table.

How do I convince my lead that its not that great an idea to try to fit everything into one table. His justification is that too many tables is a maintenance nightmare.

Or is there something wrong with how I percieve the design?
slowhand Send private email
Monday, April 21, 2008
 
 
It's obviously a poor design for most things but you didn't really give us enough info to know for sure.

I personally would not use a space character as a primary key because it isn't apparent in database tools that it is there or how many are there. I would use a character that means "no op" or "all". In the past I have used a an asterisk to imply "all values". It is a single character, already has that meaning elsewhere in programming/SQL, and has no case sensitivity issues.
dood mcdoogle
Monday, April 21, 2008
 
 
Heh, heh.  "Too many tables is a maintenance nightmare", so only have ONE table.

And that ONE table then becomes... a maintenance nightmare.

It's DLL hell replaced with Registry Hell all over again!
AllanL5
Monday, April 21, 2008
 
 
Go to Google Groups.

Lookup either "OTLT" or "One True Lookup Table".

You'll see tards and yards of commentary on why this is a bad idea,  and what problems can be expected if it is implemented.  Sone of what's written there might be convincing to your boss.

However,  there a lot of former bosses who never got convinced that OTLT is a bad idea.  Even after the company was sold in bankruptcy.
Walter Mitty Send private email
Monday, April 21, 2008
 
 
Of course the enterprise way is to put everyhting into a single xml file and then store that in a blob.
The one true record style!
Martin Send private email
Monday, April 21, 2008
 
 
Anyone who thinks that sort of thing is a *good* idea isn't going to be convinced otherwise.  People who care what ideas are good and bad will already know that it's a bad idea to cram every lookup into one table.  (Most will know because they once hit on the idea, tried it, and realized that it ends up being more of a pain than separate tables.)

I see two choices for you here:

* Hold your nose and live with it, pointless as it is.

* Look for a new job.
Kyralessa Send private email
Monday, April 21, 2008
 
 
I think the only tack to take is to explain that maintenance nightmares ultimately arise when the model doesn't match the realities of the data. If exactly one key value is needed to uniquely identify an object, then it should live in a table where it's identified by exactly one key. Too few is bad, too many is bad.

In other words, you can't pick a format that is "easy to maintain" and then hammer the data into that format. You have to pick the format the data is suited for, because that is the only format that will be easy to maintain.

Maybe that sort of language could help? Otherwise you'll just have to lay out a specific scenario where the proposed model leads to pathological behavior.
aph Send private email
Monday, April 21, 2008
 
 
HA !  Im stuck right now with a "One true lookup table" with about 12000 rows so I know how you feel.

However, being lead architect (NO I DIDNT DO IT) I can change it if i wish. However, its #200 on my to do list.

I agree with showing some articles on how this is bad, and is considered a common "noob" mistake.

Joe Celko is the man I look to for database design. Here is a small article about this subject. (Link is flaky)

http://www.dbazine.com/ofinterest/oi-articles/celko22
Anon cause I fear the real world
Monday, April 21, 2008
 
 
Actually, there's another option besides the two I mentioned: Find a way to abstract away that table so that you don't have to see its ugliness.

For instance, perhaps you can come up with a good static class that'll get your various sets of values, so you can just say GetCountries() or GetCustomerTypes() and have it go to town without having to see the nasty table.  Or maybe you can create a set of views of the table that give you just the values you really need.

You can make a lot of ugly stuff more palatable this way, by hiding it behind a more pleasant surface.
Kyralessa Send private email
Monday, April 21, 2008
 
 
"Or maybe you can create a set of views of the table that give you just the values you really need."

You can do this in reverse.

Create a separate lookup table for each type of code, and then create a view that is the union of all the separate code tables,  with an extra column that indicates the type of code.  You will find that accessing the individual tables is both easier and faster than looking through the union view,  but you have the union view for the rare situation where you are given a code but not given the code type.
Walter Mitty Send private email
Tuesday, April 22, 2008
 
 
I think the root of your problem is that you have a team leader.

This is why I'm a sole proprietor. I'd rather make my own bad  decisions than let some overpaid egomaniac make bad decisions for me. And I can arrive at a consensus far more quickly if I don't have to convince anyone else except the client to agree with me.
Rowland
Tuesday, April 22, 2008
 
 
"This is why I'm a sole proprietor. "

That's great for you (for now). The rest of us typically don't have that luxury. And if you ever end up growing beyond your single employee model then you won't have that luxury anymore either.  ;)
dood mcdoogle
Tuesday, April 22, 2008
 
 
I think this sort of thing is OK (even preferable) in certain situations. For example, if your database is deployed across 50 different servers and making schema changes is a very expensive proposition that is only allowed once or twice a year, and all you need to do is save one particular preference for something, it might be your only choice.
Greg Send private email
Tuesday, April 22, 2008
 
 
... and if you're seriously going to change jobs over something like this, well, good luck with your career.
Greg Send private email
Tuesday, April 22, 2008
 
 
Reply to dood mcdoogle:

It wasn't a luxury at the time. It was a necessity. I couldn't compete with H1B workers. I couldn't convince an Indian hiring manager to consider hiring me instead of his brother in law from Bangalore. Skills don't matter. Competence doesn't matter. Even price doesn't matter that much. And too much experience only means you're too old. It's discrimination pure and simple. Everyone I knew was in the same boat so my networks made no difference.

By the time they started hiring American citizens again the point was moot. My resume didn't have enough work experience with the very latest fad technologies. My age  was against me. And I was already well up the learning curve of being an independent. And we all know as soon as there are more visas the American programmers will be out on the street again.

If a client needs a job done I can get it done and get it done well for less time and money than it would take corporate America. And I won't rip him off or jerk him around like some guy in Russia will probably do. Both sides win and there's no middleman.

The corporate world is poison. There's no upside to it, none at all. It used to be it offered steady work while it was undermining your soul. Now that it no longer offers steady work there's nothing at all on the plus side of the ledger.

I won't ever hire another technical person as an employee. I don't want to be in employer-employee relationship with anyone who is my intellectual equal. If I need one I'll partner with one or do a joint venture.

If I wanted to boss underlings around and toady up to weaselly arseholes in expensive suits I'd have gone into management many years ago. I had the chance in 1994 but I just couldn't stomach it. I decided to keep my basic decency. I paid a steep price for that for a while there but I think I came out ahead in the end.

I don't need to grow into a big concern. I don't need to be a big shot. All I need is to make a living, keep my self respect and be on good terms with my fellow man.
Rowland
Tuesday, April 22, 2008
 
 
@Greg

This is not such a scenario. The database is on one server. But I have a feeling that my team lead got the idea by looking at such a system where the database is deployed on several servers :D

Also, I do not understand why you'd think that I am going to change jobs over this. All I want is to put forth a solid reason to not use such a table and deal with a maintenance nightmare. If my idea is not bought, tough luck but at least I tried.

I want to thank everybody for their opinion.
Slowhand Send private email
Tuesday, April 22, 2008
 
 
This type of database design for one isnt normalized. Its possible to have unique keys, but duplicate value data for one or more of the indivisual key columns.

Second, what you are doing is creating a key constraint across four key columns. For this to be reliable, you would need to either have to make every key column an identity column or connect them as foreign keys. In the first case you are just generating random keys representing non-unique values. Again, not normalized.

In the latter case makes no sense to be mapping these foreign keys to other tables, then each other, then to values. Those values need to be store in separate table with the primary keys they are connected to. That would normalize the table, plus redi=uce the need for emty value spacer columns. What you can do is keep all four value keys as they are in this table and use that as your mapping table. Remove the values. Now, create four tables for the other values...or two, and double up. What you now have is a table you can join to the other four to quickly get any kind of relationship you need, as well as the unique values from those tables. It also reduces redundency, in that each value and each combination of four values can be reused without duplicates. If you need empty values, create one with an emoty value in each of the four data tables. Much easier to manage plus expand on. - Ranger
Ranger
Wednesday, April 23, 2008
 
 
@Slowhand

Somebody suggested changing jobs, which is not uncommon even when the perceived employer transgression is something along the lines of providing free coffee but not their favorite brand. I was reacting to that post, not yours.

Yes, I think many issues like this take on far, far different meanings when viewed from the perspective of dozens to hundreds of servers spread across the country. In our world, schema changes are a HUGE deal, so we strive to minimize them any way we can.

All the best.
Greg Send private email
Wednesday, April 23, 2008
 
 
Walter Mitty,

"tards and yards"

One of the best typos since the creation of "borker" on Janet Ruhl's old consultant's board.
.
Wednesday, April 23, 2008
 
 
Let me be sure I understand.

You have a table with four key/value slots, and only one of them is filled in for each row? Do you ever have more than one of the slots filled in?

IANADE (I am not a database expert) but something doesn't seem right, a "database smell".  At any rate, I would not like to program against this schema.  In particular, ad hoc queries would be a nightmare.

Maybe one key/value and a selector say which of the four kinds of key/value, assuming only one key/value pair is ever filled in.
.
Wednesday, April 23, 2008
 
 
"It wasn't a luxury at the time. It was a necessity. I couldn't compete with H1B workers. I couldn't convince an Indian hiring manager to consider hiring me instead of his brother in law from Bangalore. Skills don't matter. Competence doesn't matter. Even price doesn't matter that much. And too much experience only means you're too old. It's discrimination pure and simple. Everyone I knew was in the same boat so my networks made no difference.

By the time they started hiring American citizens again the point was moot."

"And I won't rip him off or jerk him around like some guy in Russia will probably do. Both sides win and there's no middleman."

Let me substitute some races to show this post in its true racist colors. My substs in square brackets [].

"It wasn't a luxury at the time. It was a necessity. I couldn't compete with H1B workers. I couldn't convince an [BLACK] hiring manager to consider hiring me instead of his brother in law from [Africa]. Skills don't matter. Competence doesn't matter. Even price doesn't matter that much. And too much experience only means you're too old. It's discrimination pure and simple. Everyone I knew was in the same boat so my networks made no difference.

By the time they started hiring [WHITE ANGLO-SAXON descendant] American citizens again the point was moot."

"And I won't rip him off or jerk him around like [A JEW] will probably do. Both sides win and there's no middleman."
dd
Saturday, May 17, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz