• Q (unregistered)

    Yeah, I mean, why clutter your database with a bunch of confusing many to many relationships when you can parse a CSV, especially since you have the labor saving device of magic numbers to represent some of the longer strings of values? Who the hell wants to do a join anyway? I mean, if you are going to make the effort of making it easy to do SQL injection attacks, why not lump everything you can into one table. Of course, it will make it difficult for them to figure out who the hell is involved in any given event, but hey, they probably just want to drop the table for you anyway. :-)

  • Barry E. (unregistered)

    I can't imagine the code required to tell what meetings a specific person has for the week.

    You'd have to parse every strIDWho of every record in tblCalendar?!

    At least he has a primary key ID column. Guys here would query the table based on datDate, datStartTime, datEndTime, etc. to find the record they wanted to update.

  • Larry Osterman (unregistered)

    Ok, I give up, I can't see why on EARTH this would be done. Yech.

  • Tim Cartwright (unregistered)

    I am sorry, but that is beyond horrible, and the programmer responsible should
    have their computer taken away, and be flogged for impersonating a software
    developer. The amount of flaws in this design goes so far beyond the norm that I
    can not begin to list them in this blog.

    Tim Cartwright //Will write code for food

  • Ryan Farley (unregistered)

    Ouch. That one is great. I'd love to see the code that uses the table, that's got to be another fine peice of work.

  • Jason (unregistered)

    The file that uses this is named Code.bas

  • Jason Hasner (unregistered)

    I just wrote an asp.net app that hits a database that stores foreign keys in a comma separated list. I designed both the app and the database and doing it that way was the right thing to do (or at least "a right thing to do"). I'd tell you why if I had time. Short answer is I put them in the Session to populate a checkbox list.

    This table is a poor design, but take away the one strWho column and it's ok. I see worse on a regular basis that was written by people who I think are very smart. It's just a matter of having too much to do, so you get it "done" even though it may use a bubble sort instead of merge sort.

    As for datDate, I refuse to blame a guy for picking a naming convention and sticking to it. Otherwise, you say things like "strings will start with str.... unless of course it's obviously a string".

    So tell me, Tim (or anyone else), how would you design a database that had to do something similar if it were you? Unless you are a lot smarter than me, anything you come up with is going to have some tradeoffs with this solution.

    Jason Hasner // Will write code for a lot of money

  • Jason Hasner (unregistered)

    Q: why clutter your database with a bunch of confusing many to many relationships when you can parse a CSV

    A: It was done this way because not every person who attends a meeting is in your database.

    --------------------------

    Q: ...make the effort of making it easy to do SQL injection attacks...

    A: If I was designing an internal callendar app, I wouldn't be all that concerned about SQL injection attacks. My boss also would not want me to spend my time worrying about it. First of all, it's clearly internal so some random jackass can't hit the server, second of all, the worst thing that can happen is you lose your calendar. Not exactly your SSN or bank account number, is it?

    --------------------------

    Q: I can't imagine the code required to tell what meetings a specific person has for the week.

    You'd have to parse every strIDWho of every record in tblCalendar?!

    A: yes you would, but it would be about as fast as a join.... maybe faster. here's the code:

    Select *
    from events
    where strIDWho like '%,@user_id_or_name,%'
    and datDate > @begin_date
    and datDate < @end_date

    --------------------------

    Q: The file that uses this is named Code.bas

    A: if it's the only file in there, then who cares. otherwise, it is pretty damn funny. you could add one and call it code2.bas or morecode.bas.

    --------------------------

    I already have a gmail account, thanks anyway. I love this site, keep 'em coming. My favorite was the FileMatrix one. It made me laugh out loud.

    http://thedailywtf.com/archive/2004/06/02/188.aspx

    -- Jason

  • Shawn (unregistered)

    datDate is actually a pretty reasonable column name - considering Date is a keyword.

  • bert (unregistered)

    “If I was designing an internal calendar app, I wouldn't be all that concerned about SQL injection attacks. My boss also would not want me to spend my time worrying about it. First of all, it's clearly internal so some random jackass can't hit the server; second of all, the worst thing that can happen is you lose your calendar. Not exactly your SSN or bank account number, is it?”

    Wow…are you that ignorant?…do you think that hackers just sit on the internet trying to get into your web server? Humm ok let’s say they get in now they have access to your intranet and you better be concerned about SQL injection attack. Also many attacks occur form people inside the company.


    You are correct someone trying to break into the database is not interested in the event data. I bet the guy who wrote this app would be the same kind of person that runs the web app using the SA account…and if your lucky maybe the password is blank.

  • josh (unregistered)

    "-2, 2, 4, 5"

    where strIDWho like '%,5,%'

    And don't forget to look for the negative ones matching the department that employee is in (however you determine that), and for any containing a 0.

  • Alex Papadimoulis (unregistered)

    Jason,

    A CSV list of foreign keys in a column is an absolutely terrible abuse of a Relational Database system. The whole point of a relational model is to express your relationships. There is not a single case ever where this would be appropriate.

    In tblCalendar, there should have been a StaffAttendies table (EventID,StaffID), along with a NonStaffAttendees table (EventID,Attendee).

    And for the naming convention, it's completely absurd. There is no String ("str") datatype in SQL.

  • datGuy (unregistered)

    Surely he should at least use proper english. datDate should be ThatDate. :-)

  • Aiax (unregistered)

    Jason,

    So who do you work for? Sounds like I could create a job opening for myself pretty easily, given your level of expertise in database architecture.

  • Monday (unregistered)

    "It was done this way because not every person who attends a meeting is in your database."

    tear Left Outer Joins get no love.

  • Tim Cartwright (unregistered)

    "Q: why clutter your database with a bunch of confusing many to many
    relationships when you can parse a CSV?

    A: It was done this way because not every person who attends a meeting is in
    your database. "

    OMG, WHY use a RDBMS then at all? Use your CSV and be done with it.

    "A: If I was designing an internal callendar app, I wouldn't be all that
    concerned about SQL injection attacks. "

    Really? you must be joking? right?, cmon, you're kidding me...., right?

    "Q: I can't imagine the code required to tell what meetings a specific person
    has for the week.

    You'd have to parse every strIDWho of every record in tblCalendar?!

    A: yes you would, but it would be about as fast as a join.... maybe faster.
    here's the code: "

    PLEASE, why are you propogating the insanity, and calling it status quo?
    I could design a better calendar database in my sleep.




  • Jason Hasner (unregistered)

    A lot of people seemed to miss the part where I said it was a bad design: "This table is a poor design, but take away the one strWho column and it's ok."

    I didn't say there was nothing wrong with it. My point was that it was not on the same level with some of the other things I see here and elsewhere.

    "Wow…are you that ignorant?…do you think that hackers just sit on the internet trying to get into your web server? Humm ok let’s say they get in now they have access to your intranet and you better be concerned about SQL injection attack. Also many attacks occur form people inside the company."

    I guess I must be. That level of security is IMHO potentially appropriate for that application in SOME technical and corporate environments. I hereby concede that if someone with malicious intentions had access to their intranet and knew how the app worked then they could completely hose the calendar application's events table. If you think not losing sleep over that possibly is completely ignorant, that's ok with me. I don't think there is a right or a wrong. It’s just weighing the risk vs the effort, which is a judgment call. We can just disagree.

    "You are correct someone trying to break into the database is not interested in the event data. I bet the guy who wrote this app would be the same kind of person that runs the web app using the SA account…and if your lucky maybe the password is blank."

    So now since I said this one table was not as bad as everyone made out (I think the best one was "... and be flogged for impersonating a software developer") that means I must condone every bad security practice? Hardly.

    "where strIDWho like '%,5,%'"

    Yeah, that would sure be a tough one to get around. How would you ever remember to put commas on either side of the string? Again, it’s not a good design, but it’s not that bad.

    "And don't forget to look for the negative ones matching the department that employee is in (however you determine that), and for any containing a 0."

    That's actually a good point I didn't think about at the time. OK, I would agree that having department ID's and Emp ID's in the same field is always a really REALLY bad idea.

    Again though, I wasn't saying this was a good design, just not as bad as it was made out to be. It was obviously done be someone who probably didn't know all that much about databases, but if I waited until I mastered a skill before I tried to use it, I sure wouldn't get much done. I just didn't think it was on par with code I see all the time. Things like an if / else where both clauses contain the same code.

    "In tblCalendar, there should have been a StaffAttendies table (EventID,StaffID), along with a NonStaffAttendees table (EventID,Attendee)."

    I agree, that's the right way to do it.

    "And for the naming convention, it's completely absurd. There is no String ("str") datatype in SQL."

    Well, no shit. Thanks for the heads up on that one. Give me a second while I go fix my code.... C'MON! Try to pretend like there is a string datatype in some sort of language somewhere. OK, lets try again.


    As for datDate, I refuse to blame a guy for picking a naming convention and sticking to it. Otherwise, you say things like "[datatype]s will start with [decided upon prefix].... unless of course it's obviously a [datatype]".

    Clear it up?

    "So who do you work for? Sounds like I could create a job opening for myself pretty easily, given your level of expertise in database architecture."

    Hey jackass, maybe if you weren't such a JACKASS you wouldn't be looking for a job. Guess what, I'm not a DBA. I'm sure that doesn't shock anyone. You want to say something constructive that I can respond to, shoot. Otherwise, bugger off. My DBA can beat up your DBA any day of the week.

    I’m really good at what I do. When our parent company closed the office I worked at, I was the only technical guy to get an offer to relocate if I wanted to. Everyone else just got let go (about 13 people).

    "tear Left Outer Joins get no love."

    lol. That's one of the questions I'd ask when interviewing people (recent CS grads or interns) who put database experience on their resume. I'd ask "What is an outer join." and almost none of them would know. I decided later to change it to "how would you get the data from [something that required an outer join].

  • Jason Hasner (unregistered)

    "PLEASE, why are you propogating the insanity, and calling it status quo? I could design a better calendar database in my sleep."

    I put the code so that Barry E., who wrote:

    "I can't imagine the code required to tell what meetings a specific person has for the week."

    could imagine the code. I'm not trying to propagate any insanity. Sorry for the confusion.

    Maybe many of you work with more pristine software than I do. But hey, we can't all work at Microsoft.

    Many of you may be better at your given specialty than I am at that specialty, that's ok. I've had to become proficient in a lot of different things at the price of mastering any one of them. I just get the impression that you would take the time to rewrite the application "the right way". That's not a realistic option in my world and for most people.


    Most of you seem to think that anything that wasn't done using best practices is totally worthless. I am saying that the calendar app that used that table was something that I could modify/fix/enhance without too much trouble and I wouldn't spend very much time bitching about how horrible it was.

  • Alex Papadimoulis (unregistered)

    Jason,

    These truly are the workings of either a lazy or incompetent programmer. There are three incredibly powerful tools avaiable to every software developer: Books, Usenet, and Documentation. There is no excuse for this type of development

    If the programmer has never worked in a database before, then he should pick up "SQL FOR DUMMIES" or another book he's comfortable reading. I'm not being insulting -- I love the FOR DUMMIES series.

    Software development is a profession and a profession is a big part of ones life. It's not "just a job" like burger-flipping at McD's. If a programmer doesn't want to read a book outside of work because he gets enough "computer stuff" in all day -- then he's in the wrong profession. He should find a career he enjoys, instead of a "job."

    And hell, even if it's just a "job," have some pride! Hell, the ice-cream cones I produced in my burger-flippin' (ice-cream flingin?) days were pretty damn good.

    -- Alex

  • Jason Hasner (unregistered)

    "These truly are the workings of either a lazy or incompetent programmer."

    That very well could be.

    "There is no excuse for this type of development"

    What if this was something that the IT management couldn't spare the personnel or budget to get done? What if somebody in sales who was self taught stepped up and said "I'll do it." and got it done in short order? I would hope you could look at it then and say, "you know, that's not a half bad shot at it". They got it done, it works. We don't know the rest of the story, and without knowing the circumstances, I have a hard time judging it too harshly.

    But now I'm taking all the fun out of daily WTF, and that's not my intent.

    I agree that for some of the people who write code, computers are a big part of what defines them. I know it defines me more than it probably should.

    I just don't enjoy taking pot shots at a company's internal tools. I think those are developed with a different philosophy and by a different type of person than programs that are sold to the world by companies who are in the business of making software. I think they should be held to a different standard.

    If this was a table from some scheduling application's database that they sold as a competitor with MS Exchange server or something, I would have joined in with the chorus or people calling for a public flogging.

  • Fregas (unregistered)

    Yes, we have also have a table that has a column with a comma delimited list in it at our company. It was put in there by a VB programmer with 10+ years of experience. He did it as a "quick fix" even though he is very database saavy and aware of the rules of normalization (1nf, 2nf, etc.)

    Don't ever do this.

    Now I'm in a situation where I need to filter on this field. Every try to do a filter on "3,64,8,21" ? It sucks. I'm going to probably have to go and rewrite this part, but at least most of his other code is solid.

    I learned NOT to do this in my FIRST year of development. I don't see how this is a short cut because it is NOT difficult to model a many-to-many relationship using a seperate table. You can then do a simple join on this other table. Its easy to build, easy to populate, easy to report on and easy to program against.

    This is really basic folks. I don't understand the posts here that are excusing this kind of coding. Database design just isn't that hard. It doesn't take a lot of forethought or WORK to design your database correctly. And when you do "shortcuts" like the above, it causes a lot more work and frustration for yourself (or a future developer) later on.

    Good god. How hard is it to do something like this:
    CREATE TABLE CalendarToUser
    (userID int not null,
    calendarID int not null)




  • Fregas (unregistered)

    Oh and "They got it done, it works" just isn't good enough. The problem is that later on, when changes come, (and they will) it won't work anymore.

  • Mike Dimmick (unregistered)

    Nope, nobody's criticised this yet:

    "LIKE '%xxxx'" isn't sargable. That's a SQL Server term for 'search argument' - the server's ability to use an index. Even if you defined an index on that column, the query couldn't use it. If the wildcard's at the other end, it's sargable.

    If a suitable index was defined on the correct many-to-many relationship, SQL Server would likely blitz your nested loop design simply by being able to do a direct lookup.

  • Lastknight (unregistered)

    "where strIDWho like '%,5,%'"

    That doesn't function.
    Let's see this: "1, 3, 5, 9, 12".
    With that syntax you cannot get 1 and 12. No comma before the first, no comma after the last.

    My 2 cents. M.

    ;)

  • A *real* programmer (unregistered)

    well.. A real programmer uses C/C++/or some portable scripting language. I pity you fools learning the Microsoft languages. The fact that unix systems are gaining popularity puts you guys out of a job. Munich switched to linux, and the rest are following. Good luck in the unemployment office folks. I'm going to make my few thousand for the week.

  • Jason Hasner (unregistered)

    A real programmer doesn't have to put much work into learning a new language, coolio. You have no idea how clueless you look to the people that read this.

  • FatBoy (unregistered)

    Hey, what's wrong with using Outlook for meetings ?

  • justsomedude (unregistered) in reply to Alex Papadimoulis

    Wow, I'm amazed at the big headedness in this thread.

    Jason I hear what you're saying and reading the responses makes me wonder how many people commenting actually have jobs in the real world...where politics, less-than-compentent colleagues, and workloads frequently throw 'ideal' and 'common sense' right out the window.

    Saying there's no excuse for this type of development demonstrates to me a lack of experience working in industry. Not saying it's OK to build such crap, but it's not always as simple as some are making it out to be.

    When you're in acedemia, doing things the right way every time is the whole point, but in the real world of large business there's a helluva lot more going on than just ensuring best practices; as much as I hate to say it.

    in theory there's no difference between practice and theory, but in practice there sure as hell is

    I'm sure everyone here agrees the calendar table isn't designed correctly, but I see a heck of a lot worse garbage coming from our IT/AD group who ARE the professionals mentioned in Alex's post.

  • Tux "Tuxedo" Penguin (unregistered)

    Submitter makes fun of My worshippers. Therefore, I Tux "Tuxedo" Penguin, god of every sysop, ruler of the Internet, declare this submission invalid.

Leave a comment on “tblCalendar”

Log In or post as a guest

Replying to comment #:

« Return to Article