Comment On Pretty Simple

OMG MY EYES!

Wheres my hammer...?

Time to refactor huh ?

Yeah. I was gonna say that.

The 'pretty simple' solution is to rework it into something that isn't so bloody awful.

supposing that 0 is for "can sell":


"select * from agencies where SUBSTRING(strProductLines," . $prodID . ",1) == 0"

Not that bad.

I wonder which is faster: a straight join on three tables (agencies, productLines, agencies_producLines) or this hack ?

Still, it's not what I would do, unless the performance advantage of the SUBSTRING version would be big enough.

"see, take a look at this column, the NVARCHAR(2000) one called strProductLines?"

When I read this line, I knew the kind of fate that awaited me by the end of the article.

I often wonder at what point does something like this seem to be a good idea. There must be drink / drugs / serious mental impairment involved somewhere.

I just don't believe!

I love how, after 2000 product lines get added, the entire system breaks down.

Refactor, ASAP! If you can't, the only recommendation I can give is RUN.

I like how the original developer dismissed a normal database table because it "wastes too much space" - only to replace it with a VARCHAR(2000) column. That's 1337 programming alright!

Then again, the logic behind this is actually "pretty simple" as well, if you think about it:
A table takes up a whole page in phpMyAdmin, but the single strProductLines entry is only one line. Therefore, it must obviously be much more efficient...

They can NEVER sell a product!
"... '0' means they can't sell, '1' means they can't, '-' means there is no such product with that ID..."

I think it is a typo.
Alex, I have failed you!!

Yes, but getting a list of ALL product IDs that an agent can sell? Loop through 2000 selects?

Oh, that's horrid.

That said, some people do reach for relations tables at the slightest provocation... they're not always the best solution. (A bit like XML. Actually, scratch that, XML is almost never the best solution.) But I kinda think they might have been in this case.

emil:

supposing that 0 is for "can sell":


"select * from agencies where SUBSTRING(strProductLines," . $prodID . ",1) == 0"

Not that bad.

I wonder which is faster: a straight join on three tables (agencies, productLines, agencies_producLines) or this hack ?

Still, it's not what I would do, unless the performance advantage of the SUBSTRING version would be big enough.

SUBSTRING version will require full index search, and will probably be tens if not hundreds times slower then joining on any sane relational DB.
It will be faster if you use flat file as your DB though :)

Of course they should have used XML!

<ProductLines>

  <Product_1>

    <SellCode>1</SellCode>

  </Product_1>

  <Product_2>

    <SellCode>0</SellCode>

...

</AgentLines>

'0' means they can't sell, '1' means they can't

My God, it's full of teachers!

T $:

"see, take a look at this column, the NVARCHAR(2000) one called strProductLines?"

When I read this line, I knew the kind of fate that awaited me by the end of the article.

Ever had such a varchar field promoted to text, since there where some cases where the text would get to long? (they used XML-Fragments to store important (=needs to be searched) data...) ;)

dpm:

My God, it's full of teachers!

Dave? Is that you? This is HAL speaking.

"select * from agencies where SUBSTRING(strProductLines," . $prodID . ",1) == 0"

That's so PHPish - not knowing anything about parameter substitition and thus always introducing sql injection.

That's why nobody likes PHP, if not PHPist himself.

At this point you move from "facepalm" to "facedesk".

crphed:

OMG MY EYES!

The goggles do nothing!

captcha: saluto

It's people like this who made me stop helping out on web design forums. Here is a dramatization:

Austin316_420_69: How would I (insert fairly simple task)?
Akatherder: Just use a (insert fairly simple solution).
Austin316_420_69: Well that won't work because my table is set up like (insert tables configured like this article).
Akatherder: I would redesign your database to look like (insert reasonable suggestions). Then you should be able to solve this issue and other issues more easily.
Austin316_420_69: The database is already created and it works. I just need help with this one thing. I'm not going to redo my whole site because you don't know how to do this.
Akatherder: Well the only way you could do it is by (insert unmaintainable, shameful, half-assed hack).
Austin316_420_69: Awesome thanks!

I like the substring in where clause the most. Otherwise Refactor or RUN as was already said. I would hang myself if forced to work with such system.

This is where you hunt down the original developer and stab him to death with toothpicks.

Pretty simple indeed - just drop the table & burn the backups (if they exist that is - backups can be such a "waste of space") then voila! You get to implement a Brillant new system!

What?? Noone spotted that the beginning of the string

012-3100000100110000001000000103000001001100000-10110--

does not correspond to

Value 011-3100...

I thought you guys were programmers :-)

I would use something like...

SELECT strProductLines FROM Agency WHERE agent_id=$whatever
[transform strProductLines into a comma-delimited list of product_ids we are interested in]
SELECT * FROM Products WHERE product_id IN ($list)

followed by a petition to refactor.

Are you mad? What is faster joins, or this hack? I think hack is being kind.

With joins you can use indexes which is lightning fast if you get them right. I suppose you could create an functional index (if you can do that with the DB in question), but basically the whole thing sucks.

I amazes me that people come up with these kind of non relational database solutions, and yet use a relational database. If you don't like the way databases work, then DON'T F*&KING USE ONE DICKHEAD.

Best way to do so is to add a field in the product line table NVARCHAR(2000) (2000 Agencies should be enough) with the same technics.
Then it's only a matter of modifying programs to update both fields at the same time....

you forgot to close your <sarcasm> tag.

And in the case you were being serious:

If the schema can be changed, and your "solution" assumes it can, then you do it CORRECTLY, and use a damned relation table. You know, like they teach you in Database Design 101.

There is just no excuse for garbage like this.

Standard [url=http://technet.microsoft.com/en-us/library/ms964717.aspx]COBOL[url] just use the redefine statement and some English and you're done

TheRider:

dpm:

My God, it's full of teachers!

Dave? Is that you? This is HAL speaking.

Clbuttic

Shinhan:

When I showed this article to my colleague he said this looked elegant and nice.


He also likes to concatenate the foreign keys in one field (comma delimited). This is what I had to write to connect two tables whose only connection were comma delimited foreign keys:

WHERE FIND_IN_SET(SUBSTRING_INDEX(t1.category_array,',',1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',2),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',3),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',4),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',5),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',6),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',7),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',8),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',9),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(t1.category_array,',',-1),t2.category_array)

Shinhan, I'm forced to say...I'm so so so sorry for you.

KenW:

emil:

supposing that 0 is for "can sell":


"select * from agencies where SUBSTRING(strProductLines," . $prodID . ",1) == 0"

Not that bad.

Wrong. It's terrible. It's worse than terrible. The fact that you even suggest it tells me to stay far away from anything you've worked on, or for that matter anything that anyone who ever associated with you worked on.

emil:

I wonder which is faster: a straight join on three tables (agencies, productLines, agencies_producLines) or this hack ?

Still, it's not what I would do, unless the performance advantage of the SUBSTRING version would be big enough.

More evidence that everyone should stay far away from your code.

The SUBSTRING() solution is one of the worst possible ones. It not only completely removes the advantage of indexes (unless you happen to be searching for the first character of the column values), but it requires the massive overhead of a function call for *every single row* in the table. Only some kind of total imbecile would even suggest this as a possible solution; it would take a total moron to even think that this is "Not that bad.".

Please, for the sake of all that's good in the world, find yourself a new line of work far removed from computers. In fact, I'm not even sure you should be allowed near a computer, as a matter of public safety.

You tell him KenW! For the sake of us all, either:
A) Buy a book on DB design and SQL and learn how to do it properly.
B) If you can't afford a book, search the web on how to do it properly.
C) Stay away from anything with database in it.
D) To be extra safe, don't touch a computer.
E) Have a lobotomy so you can complete the process of being a total moron.

KenW:

Only some kind of total imbecile would even suggest this as a possible solution; it would take a total moron to even think that this is "Not that bad.".

non sequitur. your facts are uncoordinated.

The solution is WRONG and BAD but it's only ignorance, not morony, that is necessary to fail to appreciate that.

"Should be" and "is" are not always the same.

Shinhan:

When I showed this article to my colleague he said this looked elegant and nice.


He also likes to concatenate the foreign keys in one field (comma delimited). This is what I had to write to connect two tables whose only connection were comma delimited foreign keys:

WHERE FIND_IN_SET(SUBSTRING_INDEX(t1.category_array,',',1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',2),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',3),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',4),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',5),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',6),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',7),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',8),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',9),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(t1.category_array,',',-1),t2.category_array)

Tell your colleague I think it is a idiot. Actually, give me his full name so I can add him to my list of morons to never work with.

John:

Shinhan:

When I showed this article to my colleague he said this looked elegant and nice.


He also likes to concatenate the foreign keys in one field (comma delimited). This is what I had to write to connect two tables whose only connection were comma delimited foreign keys:

WHERE FIND_IN_SET(SUBSTRING_INDEX(t1.category_array,',',1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',2),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',3),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',4),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',5),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',6),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',7),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',8),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.category_array,',',9),',',-1),t2.category_array)

  OR FIND_IN_SET(SUBSTRING_INDEX(t1.category_array,',',-1),t2.category_array)

Tell your colleague I think it is a idiot. Actually, give me his full name so I can add him to my list of morons to never work with.

Crap, now I look like a moron because I can't type :-P That should have been "Tell your colleague I think HE is AN idiot"

What a moron... I think that maybe all this stupidity is somehow contagious.

akatherder:

It's people like this who made me stop helping out on web design forums. Here is a dramatization:

Austin316_420_69: How would I (insert fairly simple task)?
Akatherder: Just use a (insert fairly simple solution).
Austin316_420_69: Well that won't work because my table is set up like (insert tables configured like this article).
Akatherder: I would redesign your database to look like (insert reasonable suggestions). Then you should be able to solve this issue and other issues more easily.
Austin316_420_69: The database is already created and it works. I just need help with this one thing. I'm not going to redo my whole site because you don't know how to do this.
Akatherder: Well the only way you could do it is by (insert unmaintainable, shameful, half-assed hack).
Austin316_420_69: Awesome thanks!

I have been that Austin316_420_69 guy a few times back in the days I was still in college...

"I just need to get this fixed and it is finally working", and someone (actually all 7 guys I asked) told me to redisign.

I've learned since those days.. a bit ;)

It's people like this who made me stop helping out on web design forums. Here is a dramatization:

Austin316_420_69: How would I (insert fairly simple task)?
Akatherder: Just use a (insert fairly simple solution).
Austin316_420_69: Well that won't work because my table is set up like (insert tables configured like this article).

At this point you should just quote Pounding A Nail: Old Shoe or Glass Bottle? and a link to the respective basic literature.

captcha: ingenium

Some programmers do things like this because they believe it hedges against termination, since most people will find it almost impossible to alter their programs without screwing something up.

KenW:

emil:

supposing that 0 is for "can sell":


"select * from agencies where SUBSTRING(strProductLines," . $prodID . ",1) == 0"

Not that bad.

Wrong. It's terrible. It's worse than terrible. The fact that you even suggest it tells me to stay far away from anything you've worked on, or for that matter anything that anyone who ever associated with you worked on.

I think that's a bit harsh. It would work, and wouldn't need the database schema to be modified. I can't think of any significantly better way of doing the required task without modifying the database schema (which would probably break other code and need lots of refactoring of existing code).

No one else here has come up with a better answer. I'm sure they'd like to refactor, but if that's not possible ("we need this by tomorrow") and this feature isn't going to be used much, and the database isn't that busy, then the substring idea would work, until they can schedule in some refactoring.

pitchingchris:

Time to refactor huh ?

om my god yes

Ilyak:

"select * from agencies where SUBSTRING(strProductLines," . $prodID . ",1) == 0"

That's so PHPish - not knowing anything about parameter substitition and thus always introducing sql injection.

That's why nobody likes PHP, if not PHPist himself.

You're probably even a worse idiot, if you're blaming PHP for this. It's unsafe practices by the programmer that make this an issue, not the language. I wouldn't hire you as a programmer, just because of that kind of golden hammer crap.

oh, how about a table with 25 agencies (not many records to iterate over), and another table with 2000 active products, plus 13 states for each product, in an application that was supposed to manage the sale of only one product initially and with 2 days time allowed for upgrade to an application supposed to manage more than one product.

The greatest issue with that solution is not searching, concurrent updates. That would be a bitch to write, so I believe the author did not have much choice in the matter due to other constraints.

I did not say this is the best solution, but that finding the agencies that can sell a product is not that difficult.

Take a break and breathe ... I have seen and had to maintain much worse in my short life. You'll do too and and at some point will have to relax and choose quiet resignation and a "let's get things moving" attitude over database normalization, Induced Tourette Syndrome and homicidal rage.

Performance could be very efficient if we just create a few appropriate indexes, like:

create index agent_prod_1 on agency(substring(strProductLines,1,1);

create index agent_prod_2 on agency(substring(strProductLines,2,1);

create index agent_prod_3 on agency(substring(strProductLines,3,1);

... etc ...

And think of the advantages in human readability! Instead of having to figure out some cryptic thing like

Agency   | Product Line     | Access      |

---------+------------------+-------------+

Denver   | bicycles         | In-training |

Richmond | bobble-heads     | No          | 

Austin   | nuclear warheads | Sell        |

etc, all you have to do is remember that, say, bicycles is product line number 204, count over to column number 204, note that "6" means in-training, and you're done! And please, don't tell me people will lose count finding column number 204. As long as you keep in mind that column 203 is "potted plants" and you remember that OF COURSE the Denver office would be code "8" on potted plants, then if you see that the column before the one you're looking at is not an 8 then you must have mis-counted. If you're too lazy to do a little work to figure it out, well ...

At least, that's the sort of explanation I typically get when I ask about crazy designs like this. Usually with an "are you stupid or just a trouble-maker?" tone of voice.

Horrible.

So if a gap is presented in the sequence from shutdown or rollbacks, then you will just get a bunch of

------

Baby Jesus cries for the original developer of this database.

akatherder:

It's people like this who made me stop helping out on web design forums. Here is a dramatization:

Austin316_420_69: How would I (insert fairly simple task)?
Akatherder: Just use a (insert fairly simple solution).
Austin316_420_69: Well that won't work because my table is set up like (insert tables configured like this article).
Akatherder: I would redesign your database to look like (insert reasonable suggestions). Then you should be able to solve this issue and other issues more easily.
Austin316_420_69: The database is already created and it works. I just need help with this one thing. I'm not going to redo my whole site because you don't know how to do this.
Akatherder: Well the only way you could do it is by (insert unmaintainable, shameful, half-assed hack).
Austin316_420_69: Awesome thanks!

Follow up:

Some_other_guy: Why the ^&#@ did you (insert unmaintainable, shameful, half-assed hack)?
Austin316_420_69: What's wrong with it? Akatherder told me I should do it like that!
Some_other_guy rolls eyes...