Comment On The Omni ID

Guy's got a point. Wouldn't want to hit any hard-coded table limits in the database.

Since I don't understand why Joshua wanted to change the DB design that handled everything else correctly by a more senior developer, that must be the WT... wait I get it now

wfwaf:

Guy's got a point. Wouldn't want to hit any hard-coded table limits in the database.

Given the insanity of this system and all of the juvenile delinquents it'll create, the 18x10^16 of an unsigned bigint is woefully insufficient.

Soooo not the right way to do this, but given Roy's mandate...

Screen format for ContextFieldId: 'TableName|Data'
[1] 'Case|Big-Int-Value-here'
[2] 'Hearing|nvarchar[16]-here'
[3] 'Motion|int-here'
[4] 'Pleading|nvarchar[32]-here'

or, if you have the luxory of a limited number of tables, use C, H, M and P respectively as the prefixes.

Again, sooo the wrong way to do it but sometimes you gotta play by someone else's rules.

And Joshua's only mistake: going back to the original developer with a suggestion on how to enhance his design instead of just doing it.

only mistake: going back to the original developer with a suggestion on how to enhance his design instead of just doing it.

Truest thing I've heard in a long time.

Re: Snoofle

We actually use something sort of like this in some cases, except we have to columns - itemtype and itemid. Itemtype stores the table name, essentially, or if it's more complex than that, the object type.

Not ideal, but for instance, we wouldn't want an "images" table for every table, either.

It would double the number of tables!!

Premature optimization is the root of all evil.

snoofle:

Soooo not the right way to do this, but given Roy's mandate...

Screen format for ContextFieldId: 'TableName|Data'
[1] 'Case|Big-Int-Value-here'
[2] 'Hearing|nvarchar[16]-here'
[3] 'Motion|int-here'
[4] 'Pleading|nvarchar[32]-here'

or, if you have the luxory of a limited number of tables, use C, H, M and P respectively as the prefixes.

Again, sooo the wrong way to do it but sometimes you gotta play by someone else's rules.

And Joshua's only mistake: going back to the original developer with a suggestion on how to enhance his design instead of just doing it.

minimal fix for this particular problem: add a type discriminator column - now it's a variant table.

Why we can't just add columns everywhere for last_updated_by and add triggers i dunno - we do it here and it mostly works, except when it's oracle that does it.

only mistake: going back to the original developer with a suggestion on how to enhance his design instead of just doing it.

only the case if the original (and more senior) developer will never find out about it later and then be annoyed you didnt check with them first...

<i>Premature optimization is the root of all evil.</i>

A common misconception.

What the Bible actually says is:

"The love of Premature optimization is the root of all evil."

- Karl

"I can see why you chose this design".
He's truly a gentleman.


Or a big fat liar.

Anonymous:

only mistake: going back to the original developer with a suggestion on how to enhance his design instead of just doing it.

only the case if the original (and more senior) developer will never find out about it later and then be annoyed you didnt check with them first...

I disagree. "Better to ask forgiveness than permission", right?

Nice. Now go and fill out the survey and be sure to mention why you hate the name change.

CAPTCHA: OMGURDUM

James:

Anonymous:

only mistake: going back to the original developer with a suggestion on how to enhance his design instead of just doing it.

only the case if the original (and more senior) developer will never find out about it later and then be annoyed you didnt check with them first...

I disagree. "Better to ask forgiveness than permission", right?

To Sr. Dev: "I'm sorry, I tried to sit with you a couple of times, but you were extremely overloaded, and I didn't want to bother you with something that seemed fairly simple. If you'd like, I can walk you through what I did and show you how it (already) works (efficiently and) correctly."

I personally have found that people who are protective of anyone else changing their code can *usually* be handled similarly...

Kinda missed the point here. Where is the funny conclusion? If there is not any, this is still a WTF.

CAPTCHA: TACOS, yummy!

In such a case, don't walk away from the company -- run! You're wasting your talent.

I would just add a column to the Screen table to indicate the type of ID.

snoofle:

And Joshua's only mistake: going back to the original developer with a suggestion on how to enhance his design instead of just doing it.

What a great idea! The n00b intern changes the database schema without telling anyone, thereby bringing to its knees every application that accesses that database. When the howls of frustration and rage from the users reach the ears of upper management and the scapegoat hunt begins, our hero will be scampering down the road so fast his Reeboks catch on fire.

FredSaw:

snoofle:

And Joshua's only mistake: going back to the original developer with a suggestion on how to enhance his design instead of just doing it.

What a great idea! The n00b intern changes the database schema without telling anyone, thereby bringing to its knees every application that accesses that database. When the howls of frustration and rage from the users reach the ears of upper management and the scapegoat hunt begins, our hero will be scampering down the road so fast his Reeboks catch on fire.

Unfortunately I have to agree with FredSaw on this one, you might be able to do it with code, but databases is a different story - you never know how many hardcoded dependencies might be lying around.

"What if there are more things they want to record about a case later on? We'll keep adding tables and adding tables and before long there will be double the tables!"

True. I mean, a database isn't exactly efficient for storing this kind of stuff (data). Better go with something more universal, like Word. Any updates you'd need to do can be conveniently and quickly done with a find/replace all. Lightning fast, end users could even do it, and they wouldn't need to know anything about SQL.

The best part is, if they want to start formatting the text as it's stored in the "Database", it's all right there man!

well - this looks like a developer saying 'tell me I'm right cos my boss is dumb'

There are merits to both sides - sadly realtional databases are cr*p at doing OO things. What the boss wanted was to have a pointer to a base class of 'case thingy' with derived classes of 'pleading', 'motion' etc. Cant easily be done

He should have got a single key type though
and then added a discriminator colum

The poster complaint is that the sql engine doesnt know about the relationship - yup it doesnt

a real WTF

COBOL statement from one of my favorite devs (20 years ago)

MOVE SPACES TO ZEERO

dont ask how it got there u dont want to know

Robert S. Robbins:

I would just add a column to the Screen table to indicate the type of ID.

Since tablenames aren't parametric in normal SQL, this wouldn't be the best solution, IMHO.

I would go with Kafkas triggers.

Stefan W.:

Robert S. Robbins:

I would just add a column to the Screen table to indicate the type of ID.

Since tablenames aren't parametric in normal SQL, this wouldn't be the best solution, IMHO.

I would go with Kafkas triggers.

Thanks, but the discriminator column is really a low impact sort of thing that mostly works. It would require apps to populate another column and a backfill, but the potential fallout of an error would be limited.

Well, you have a contextfieldid which gives a different id depending on the context...
Why not add a column called fieldidcontext which tells what the actual context is?

This reminds me of the old and no-longer-funny joke where the sportscaster is asked for scores and he says "24-14, 17-7,..." without naming the teams. We have pointers, but don't know where they are pointing.

Which leads us to Dr. Phil's question: What were you thinking?

Uh, how about an even simpler solution. Just add a new field to the "Screen" table called "type" or something like that. Type would be "Case", "Hearing", etc. You can add more types as needed. We call it a polymorphic association around here. Use 'em all the time.

vt_mruhlin:

Well, you have a contextfieldid which gives a different id depending on the context...
Why not add a column called fieldidcontext which tells what the actual context is?

Splitter!

FFS! It's "too big an application", not "too big of an application". The latter makes no grammatical sense.

It's a sad fact, but this doesn't surprise me. The vast majority of developers I have had to work with do not understand even simple relational database concepts.

I have had to deal with many systems that have a database comprised of one large table. But that must be the correct way to do thing. Why else would Excel be the #1 tool for creating and storing databases :o

I even had a "senior" developer once lecture me on how evil table joins were as they were major performance hits on the system. Well he was correct... when joining two tables with ~50K rows each, with no formal relations defined between the tables, and using non-indexed fields to join the tables. sigh.

kgj:

What the Bible actually says is:
"The love of Premature optimization is the root of all evil."

- Karl

If you're going to correct someone, get it right!

"The love of premature optimization is the root of all kinds of evil."

Matthew:

Uh, how about an even simpler solution. Just add a new field to the "Screen" table called "type" or something like that. Type would be "Case", "Hearing", etc. You can add more types as needed. We call it a polymorphic association around here. Use 'em all the time.

the problem with such an approach is that it is easy that errors sneak in so the 'type' no longer matches the content of the other columns. the best way is to enforce data integrity at the database level so that programs have no change of entering garbage.

snoofle:

...use C, H, M and P respectively as the prefixes...

Yeah! CHoMP CHoMP CHoMP!

Oh wait... maybe CHiMP... as in the million or so they have in the back room banging on keyboards. Soon they'll design a database capable of housing all human knowledge.

CynicalTyler:

Yeah! CHoMP CHoMP CHoMP!

Oh wait... maybe CHiMP... as in the million or so they have in the back room banging on keyboards. Soon they'll design a database capable of housing all human knowledge.

Or maybe CHuMP, to represent what you look like after such a silly joke. ;)

You would be very surprised at how often I see this in applications that run the entire companies. I am having to deal with this right and the Sr. Developer previously, now Corporate Solution Architect will not even return my phone calls or emails. Even his boss ignores me now.

Just me:

It's a sad fact, but this doesn't surprise me. The vast majority of developers I have had to work with do not understand even simple relational database concepts.

I have had to deal with many systems that have a database comprised of one large table. But that must be the correct way to do thing. Why else would Excel be the #1 tool for creating and storing databases :o

I even had a "senior" developer once lecture me on how evil table joins were as they were major performance hits on the system. Well he was correct... when joining two tables with ~50K rows each, with no formal relations defined between the tables, and using non-indexed fields to join the tables. sigh.

Working on an application where some of the tables hold around 1-1,5 billion records (which seems about right for juvenile delinquents...) having everything in the same table is not a good thing....

with a good datamodel you can support almost any type of application. and indexes... oh how i love indexes...

pm:

...sadly realtional databases are cr*p at doing OO things... The poster complaint is that the sql engine doesnt know about the relationship...

Well, if you don't know how to use a database, everything involving data will be cr..p, especially the final product.

If you get the chance to read Oracle manuals regarding "create view of <object_type>..." you'll find it amazing: it gives all the ignorance about the data needed by developers that don't want to bother studying how it works a database ;-)

That's pretty apt! The WTF has four stages:

The Case, The Hearing, The Motion and finally, The Pleading.

pavel:

Re: Snoofle

We actually use something sort of like this in some cases, except we have to columns - itemtype and itemid. Itemtype stores the table name, essentially, or if it's more complex than that, the object type.

Not ideal, but for instance, we wouldn't want an "images" table for every table, either.

It would double the number of tables!!

I really, really hope this is missing it's sarcasm tags.

oh how i love indexes...

Am I the only person who gets annoyed by people not knowing the plural of index is indices?

I-D:

"I can see why you chose this design".
He's truly a gentleman.

But not a diplomat. "I can see"?! Does he already, a mere junior, rate himself as capable of comprehending even the SCOPE of the original design challenge?

When dealing with insecuretins , best to get them teaching you. Then if you are skillful, you will be able to get THEM to have your idea.

oh how i love indexes...

Am I the only person who gets annoyed by people not knowing the plural of index is indices?

You are not alone ;)
shame they out number us though :(

No, both forms are correct, 'indices' is merely more elegant, coming directly out of latin. But hey, what do I know, not a native english speaker...

MET:

oh how i love indexes...

Am I the only person who gets annoyed by people not knowing the plural of index is indices?

Actually, according to Fowler, both indices and indexes are valid English, with indices being used mostly in formal and scientific situations, and indexes being used in popular or colloquial situations. But "there is considerable liberty of choice".

Eg, "the volume is rounded off by splendid indexes" and "Integral, fractional and negative indices" (Fowler's examples)

So there ;-P

alter table hearing
add enteredby varchar2(30) default user;

The real WTF is the use of Vista.

sweavo:

I-D:

"I can see why you chose this design".
He's truly a gentleman.

But not a diplomat. "I can see"?! Does he already, a mere junior, rate himself as capable of comprehending even the SCOPE of the original design challenge?

When dealing with insecuretins , best to get them teaching you. Then if you are skillful, you will be able to get THEM to have your idea.

Insecuretins are the number one plague on IT business. Just beyond them are: the big fat liars, the gentlemen, and i-ching/tai-chi-chuang/yoga CEOs.
Mere juniors are way down on the list.

Anonymous:

only the case if the original (and more senior) developer will never find out about it later and then be annoyed you didnt check with them first...

I'd say Roy's so thick he probably wouldn't notice.

Anonymous:

only the case if the original (and more senior) developer will never find out about it later and then be annoyed you didnt check with them first...

I'd say Roy's so thick he probably wouldn't notice.

"double the number of tables" is almost always a good thing.

It's pretty much impossible to create unnecessary tables and the worst that can happen is you end up with 2 tables with a one for one relationship - not a big deal really – in fact sometimes desirable if the record length is long and there is an obvious division about when different parts of the record will be accessed.

On the other hand, having unrelated information in the same table just because they happen to have the same format, causes all kinds of referential, security, and scaling issues.

Having a value in a field which might mean one thing or another with no way of telling which is just plain wrong in every way. There is no justification or excuse, Roy should be fired and so should the person who raised the original audit requirement but then did not ask to see how the change could be tracked (or signed it off without understanding it).

I'm all for shoving as much as I can into one table. In fact I shun modern databases and go with Excel only.

Have more than 10 items on an item? Simple - just add a column called item 11. Hit the max(rows)? Add a new tab. Completely scalable!

Normalization is teh suck.