• Crash (unregistered)

    Ahhh.... but with everything in the same table.... lookups are FAST!

  • Chris R. Timmons (unregistered)

    I've seen others on this blog defending some of the WTF code posted here, so I thought I'd jump in and and try to sound optimistic:

    1. The teenager who designed the database was too distracted thinking about his junior prom to learn about normalization and referential integrity.

    2. The database design team consisted of a thousand monkeys banging away at a thousand computer keyboards, and this is what they came up with.

    3. Can you say "reefer madness"?

    I'm sorry. That's as optimistic as I could get about this database...

  • Peter (unregistered)

    At a previous company I worked for, we used Clientele as the CRM app. It used a very very similar key structure, and used UUID/GUIDs for the keys. Person/Group/Organization (or PGO) instead of Co/Div/Dept. Instead of nulls, they used 0, yep zero. So Dr Bob, who works at both DowntownHospital and SuburbanClinic could not be the same Dr Bob, since the G or O would be a different GUID. Since the back end database could be access, sql server, oracle, or any odbc compliant database, there was no referential integrity at the db level, it was all in the code.

    I did not have your luxury of dismissing the product, I had to make it work. And I had to make it work seemlessly with the rest of the company's databases. That was a challenge, reverse engineering the way stuff was organized. I remember being very impressed with how they managed to lock up the access databases (which was the front end) with encryption and workgroup security. I was also very impressed that they could do what they did with access. Note, the company I worked for at that time did everything with access.

    If you had poked stuff into the supplied front end, you might find 0 instead nulls. But you are correct in noticing it is not a clean design.

    I've seen worse, don't make me come up there carrying my cow, uphill, both ways, in the snow, with my hand carved "1"s and hit you with my oxygen bottle to prove it. Please. ;)

  • DrPizza (unregistered)

    Maybe they designed one table and then got bored and just copied the design for the other tables.

  • KoFFiE (unregistered)

    @Peter - seriously - if you've seen worse YOU HAVE TO POST IT!!!

    Damn...

  • MGC (unregistered)


    * * * * *

    * * * * ****
    * * * * *

    * * * * *

    ***** * *

  • MGC (unregistered)

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

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

    --------------*-----

    -    **-----------*-----

  • icelava (unregistered)

    It's really just a RAIT - Redundant Array of Inexpensive Tables :)

  • Tom (unregistered)

    Oooh. I used to have to mess about with the back end of Clientele at my last place to create bespoke reports and the like. An unmitigated bunch of @rse if I remember correctly, and made my life a nightmare for much longer than I would have liked.

  • Cakkie (unregistered)

    I really do wonder how there join statements look like :p

    SELECT * FROM Companies c
    INNER JOIN Devisions dv ON c.CompanyID = dv.CompanyID AND c.DevisionID = dv.DevisionID AND c.DepartmentID = dv.DepartmentID
    INNER JOIN Departments dp ON c.CompanyID = dp.CompanyID AND c.DevisionID = dp.DevisionID AND c.DepartmentID = dp.DepartmentID

    -or judging by the structure they probably never heard of JOINS, en do the linking on the oldfashioned way, in the WHERE clause

    SELECT * FROM Companies c, Devisions dv, Departments dp
    WHERE c.CompanyID = dv.CompanyID
    AND c.DevisionID = dv.DivisionID
    AND c.DepartmentID = dv.DepartmentID
    AND c.CompanyID = dp.CompanyID
    AND c.DevisionID = dp.DivisionID
    AND c.DepartmentID = dp.DepartmentID
    -- If ReallyStupid = True, even add more :p
    AND dv.CompanyID = dp.CompanyID
    AND dv.DevisionID = dp.DivisionID
    AND dv.DepartmentID = dp.DepartmentID

  • Steve O. (unregistered)

    Icelava - Wouldn't that actually be Redundant Array of Inefficient Tables?

  • Randy Glenn (unregistered)

    I believe that would be a Redolent Array of Idiotic Tables.

    Redundant Array of Independent Tables also applies, with the lack of Referential Integrity constraints.

  • Vik (unregistered)

    I've seen some large ERP's (an older version of Great Plains, for one) also not use referential integrity. However, I think this has more to do with avoiding the overhead associated with integrity checking in high volume OLTP environments.

    Plus, all access to base tables is more or less controlled through sprocs, so I could see some reason NOT to use it, though maintenance and debugging would certainly suck.

    However, buddy there should really read up on normalization techniques.

    As for queries against this table, I've seen a similar structure using a bunch of ugly joins, a la Cakkie's eye glazer above, combined with a series of uglier UNION's.

    Yeargh...

  • DesperatelySeekingAnotherJob (unregistered)

    Count yourselves lucky... I'm still supporting a Clientele system... heavily bespoked... and the bespoked code is even worse...

    It's being replaced... By a web system developed by the same company... Have they learned anything at all? What do you think... How many of you have seen stored procedures that not only generate HTML, but JavaScript too?

    HELP ME SOMEBODY!!!!!

  • Collegue (unregistered)

    Transactional HTML and Jscript... bleeding... edge.

Leave a comment on “Proper Database Nondesign”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article