• (cs)

    It takes experience and a unique thought pattern to use a relational database engine for flat storage.

    CAPTCHA: Lateratus - thinking inside the server-box

  • (cs)

    Oh come on, everyone knows the real WTF is using Oracle.

    Although I suppose the screenshot of Oracle SQLDeveloper could have been part of Alex's anonymization.

  • (cs)

    So... what's with the terms starting in March? Where'd those come from?

  • DBA (unregistered)

    Maybe there are too many records in each table, so they have optimized the performance.

  • Barrett Jacobsen (unregistered)

    What application is that in the screenshot?

  • (cs) in reply to Barrett Jacobsen
    Barrett Jacobsen:
    What application is that in the screenshot?

    SQL Developer

    The first digit denotes the century (2 for the 21st century), the second and third digits are the specific year (08 for 2008, 09 for 2009, and so on) and the last digit denotes the month the term begins

    Surely this has to TRWTF? Also, do you mean the first digit denotes the century, or the millennium, neither really makes any sense, of course, but just out of curiosity...

  • NoAstronomer (unregistered) in reply to Barrett Jacobsen
    Barrett Jacobsen:
    What application is that in the screenshot?

    As per powerlord's comment, that's Oracle's SQLDeveloper. A free download from Oracle. You can also connect to MS-SQL and other databases.

  • (cs)

    [quote] The first digit denotes the century (2 for the 21st century) [\quote]

    The 21st century includes all years of the form 2_?

    Addendum (2010-07-12 10:59): God damn, I suck at BBCode.

  • Crabs (unregistered) in reply to DaveyDaveDave
    DaveyDaveDave:
    The first digit denotes the century (2 for the 21st century), the second and third digits are the specific year (08 for 2008, 09 for 2009, and so on) and the last digit denotes the month the term begins

    Surely this has to TRWTF? Also, do you mean the first digit denotes the century, or the millennium, neither really makes any sense, of course, but just out of curiosity...

    Makes perfect sense. Classes in the 20th century started with 1. Classes in the 21st start with 2. So a fall class schedule in 1999 would be 1 99 9. A spring class schedule in 2007 would be 2 07 1.

  • ThomasP (unregistered) in reply to DaveyDaveDave

    [quote]The first digit denotes the century (2 for the 21st century), the second and third digits are the specific year (08 for 2008, 09 for 2009, and so on) and the last digit denotes the month the term begins[/quote]

    Surely this has to TRWTF? Also, do you mean the first digit denotes the century, or the millennium, neither really makes any sense, of course, but just out of curiosity...[/quote]

    It's not the worst numbering scheme I've ever seen... how would you have done it?

  • JS (unregistered)

    Yawn.

    That's a boring, quite frequent pattern of misuse. Never mind the stupid semester ids, the twtf is not finding something worse like storing references to table names guarded by custom CHECKs.

  • (cs)

    I thought this explained Quest fairly well until I saw the term format was just slightly different.

  • (cs) in reply to DaveyDaveDave
    DaveyDaveDave:
    The first digit denotes the century (2 for the 21st century), the second and third digits are the specific year (08 for 2008, 09 for 2009, and so on) and the last digit denotes the month the term begins

    Surely this has to TRWTF? Also, do you mean the first digit denotes the century, or the millennium, neither really makes any sense, of course, but just out of curiosity...

    2 = 21st century 3 = 22nd century

    I suppose it works. But TRWTF is making it confusing just to cut one digit from the storage when this same database is piling on tables.

  • xxx (unregistered)

    There is no WTF in 281 tables - it's a way of creating "partitioned" tables in Oracle Standart Edition. Enterprise Edition with partitioning option is expensive.

    Table naming is a bit strange, but...

  • Bob (unregistered) in reply to Callin
    Callin:
    I suppose it works. But TRWTF is making it confusing just to cut one digit from the storage when this same database is piling on tables.

    Perhaps the reason is because it cuts one character from the table name, not from the data.

  • bl@h (unregistered)

    Wheres the wooden table and the XML, I certainly am not sending my kids to this school if they don't use best practices!

  • re:me (unregistered) in reply to Crabs
    Crabs:
    DaveyDaveDave:
    The first digit denotes the century (2 for the 21st century), the second and third digits are the specific year (08 for 2008, 09 for 2009, and so on) and the last digit denotes the month the term begins

    Surely this has to TRWTF? Also, do you mean the first digit denotes the century, or the millennium, neither really makes any sense, of course, but just out of curiosity...

    Makes perfect sense. Classes in the 20th century started with 1. Classes in the 21st start with 2. So a fall class schedule in 1999 would be 1 99 9. A spring class schedule in 2007 would be 2 07 1.

    I think he means TRWTF is that students enter some dopey, cryptic number instead of simply selecting a year and term from a drop down, or even typing in the year and 'fall' (or a numeric month) if it, for some silly reason, happens to be a text-only search screen.

  • (cs) in reply to bl@h
    bl@h:
    Wheres the wooden table and the XML, I certainly am not sending my kids to this school if they don't use best practices!
    Let us know if you find a school that does.
  • (cs) in reply to ThomasP

    [quote user="ThomasP"][quote]The first digit denotes the century (2 for the 21st century), the second and third digits are the specific year (08 for 2008, 09 for 2009, and so on) and the last digit denotes the month the term begins[/quote]

    Surely this has to TRWTF? Also, do you mean the first digit denotes the century, or the millennium, neither really makes any sense, of course, but just out of curiosity...[/quote]

    It's not the worst numbering scheme I've ever seen... how would you have done it?[/quote]

    I agree, it's not the worst. I wouldn't have done it that way, but my hunch is that it's a holdover numbering scheme from a legacy system.

  • (cs) in reply to ThomasP
    ThomasP:
    It's not the worst numbering scheme I've ever seen... how would you have done it?

    I agree, it's not the worst. I wouldn't have done it that way, but my hunch is that it's a holdover numbering scheme from a legacy system.

  • (cs) in reply to mwchase
    mwchase:
    So... what's with the terms starting in March? Where'd those come from?

    Quarter system, dude.

    Mid Sept - Mid Dec Jan - March April - June

    and then something in the summer that I don't know because I never took summer classes at a UC.

  • Nome de Plume (unregistered) in reply to DaveyDaveDave
    DaveyDaveDave:
    Barrett Jacobsen:
    What application is that in the screenshot?

    SQL Developer

    The first digit denotes the century (2 for the 21st century), the second and third digits are the specific year (08 for 2008, 09 for 2009, and so on) and the last digit denotes the month the term begins

    Surely this has to TRWTF? Also, do you mean the first digit denotes the century, or the millennium, neither really makes any sense, of course, but just out of curiosity...

    It's only a WTF to tell students that the 4-digit code means those things. As long as they enter a number and get a course name confirmation, it works fine.

    Back in 1994, my university created a touch-tone phone automated course sign-up. Students used a four or five digit course number to enroll via the phone's numeric keypad. The system responded with a voice recording. That was way better than waiting in lines!

  • Lyle (unregistered)

    Quit getting fancy with the first three digits. They're obviously year - 1800. This probably dates from long ago when saving a character made sense.

  • Kempeth (unregistered)

    Wait. According to that description they should have 3 tables per year (x2 because there are hours and data tables). With 281 tables that comes to 43 years. 2010-1968? I doubt it. So according to what magic criteria did they break them down further?

  • Phyzz (unregistered)

    That numbering system seems suspiciously similar to the one at my University.

    "C - the Century D - the Decade Y - the Year [1,4,7] - the semester: 1=Spring, 4=Summer, 7=Fall

    In the ____ system, the Centuries started with the number 0, which was equated to 1900. The Century 2000 is thus equal to 1."

    Current reg code for the upcoming fall is 1107. But after my first two semesters someone grew a brain and did put drop boxes for most places where the system wants to know this.

    Can't say anything about underlying DB but considering its speed at peak time it would not surprise me.

  • (cs) in reply to xxx
    xxx:
    There is no WTF in 281 tables - it's a way of creating "partitioned" tables in Oracle Standart Edition. Enterprise Edition with partitioning option is expensive.

    Table naming is a bit strange, but...

    Surely their Oracle databse could handle student enrollments for quite a few semesters before needing to be partitioned. Even for a really big university with 100,000 full time students, you wouldn't get more than about a million enrollments per semester. That would make each of the "partitions" a few hundred megabytes. 281 two hundred megabyte tables only amounts to 56GB of data. That's still only a medium sized database. This is a classic case of either premature optimization, or total cluelessness.

    Reporting should be fun with this database schema.

  • ObsidianRat (unregistered) in reply to Markp

    Quest was my first thought, too. It'd explain so much.

    I wonder if the discrepancy in the term format is just due to the changes made for the write-up to protect the system's anonymity.

  • (cs)

    Our university was using PeopleSoft, which had a DB Schema very similar to this. I think they were using MsSQL though. I believe the schema wasn't necessarily defined by PeopleSoft, but by the consultants who implemented PeopleSoft on their servers. PeopleSoft is still a big wtf, though.

  • (cs) in reply to ThomasP
    ThomasP:
    It's not the worst numbering scheme I've ever seen... how would you have done it?

    Used one more character and stored a full four-digit year. In fact, I was creating data formats of that sort as early as 1981, on my Apple II, anticipating the Y2K issue almost two decades in advance. Actually, by 2000 I no longer had any working hardware capable of reading my old Apple 5.25" disks, but the principle was reasonable.

  • blah (unregistered)

    ███████ works when ████ and ████████ but really ████████.

    The censorship in this article made about as much sense as on the recent South Park.

  • Pounder (unregistered) in reply to RHuckster

    Confirming the horrors of peoplesoft which was just rolled out at my university.

  • █████ (unregistered)

    Hey, that looks like my university!

    Not that I think it really is my university. Rather, I think the CIOs all play a game of "try to make sure we suck as bad as the others". U of X goes OraKill, we go OraKill! U of Y adds PeopleShaft, we add PeopleShaft.

    And as for the semester numbering, I think the lesson they learned from Y2K was let's make our date routines even more cryptic so we need to hire more consultants next time, even though we're only saving a byte now instead of two.

  • (cs) in reply to Markp
    Markp:
    I thought this explained Quest fairly well until I saw the term format was just slightly different.

    Tell me about it. It's probably a bad thing that my first thought when the semester numbering above was explained was "Well, at least there exists some explicable algorithm for figuring out the semester ID".

    I'm not sure, but my running theory is that U. Waterloo uses 'Months since first term (ever)' as a semester id. Oh boy.

    Judging by performance, I expect Quest's back-end makes this look like the pinnacle of best practices.

  • (cs) in reply to DaveyDaveDave
    DaveyDaveDave:
    The first digit denotes the century (2 for the 21st century), the second and third digits are the specific year (08 for 2008, 09 for 2009, and so on) and the last digit denotes the month the term begins

    Surely this has to TRWTF? Also, do you mean the first digit denotes the century, or the millennium, neither really makes any sense, of course, but just out of curiosity...

    It's not as WTF as those deranged systems where the day comes before the month.

  • some anon (unregistered)

    I'm surprised no one has yet mentioned the huge SQL injection vuln here: any and all queries are probably created dynamically.

    Anybody want an A+ in their algos class? :)

    captcha: consequat - not even gonna go near that one

  • (cs) in reply to blah
    blah:
    ███████ works when ████ and ████████ but really ████████.

    The censorship in this article made about as much sense as on the recent South Park.

    Give the people some credit. At least you couldn't read the original by looking at the page source. That is something...

  • moz (unregistered)

    Mine solved the "cryptic term number" problem by not running any course more than once a year and not allowing students to register for courses more than a year in advance. It seems to work well enough.

  • AC (unregistered)

    Our institution uses GradPro. While it has many WTFs (Access? Really?), the one thing I do kind of like is that the quarter ID is simply the four digit year followed by 1-4 to denote the quarter of that year like so: 20101 = winter, 20103 = summer, etc. Simple enough.

    By the by, anybody use Populi? Any serious WTFs? We're looking at moving to their system and I would love to know about any potential issues.

  • Jay (unregistered)

    Why do our databases need to have any columns in the tables at all? Just string all the data together to make the table name, and have one table per logical row!

  • (cs) in reply to █████
    █████:
    Hey, that looks like my university!

    Not that I think it really is my university. Rather, I think the CIOs all play a game of "try to make sure we suck as bad as the others". U of X goes OraKill, we go OraKill! U of Y adds PeopleShaft, we add PeopleShaft.

    And as for the semester numbering, I think the lesson they learned from Y2K was let's make our date routines even more cryptic so we need to hire more consultants next time, even though we're only saving a byte now instead of two.

    Did you vote OBummer or McPain for PresiDon't of Amerikkka 2008?

    Silly me, you're not old enough to vote yet, are you?

  • (cs) in reply to Callin
    Callin:
    DaveyDaveDave:
    The first digit denotes the century (2 for the 21st century), the second and third digits are the specific year (08 for 2008, 09 for 2009, and so on) and the last digit denotes the month the term begins

    Surely this has to TRWTF? Also, do you mean the first digit denotes the century, or the millennium, neither really makes any sense, of course, but just out of curiosity...

    2 = 21st century 3 = 22nd century

    I suppose it works. But TRWTF is making it confusing just to cut one digit from the storage when this same database is piling on tables.

    As has been said, it's not for storage but because it's also user-facing (at the very least, to admins, but sadly, usually to students as well). It's really not that difficult if you also remember that the 1/6/9 is probably because their terms start in January/June/September.

    It's really quite simple to translate, say, September 2029 into 2299. The century may be confusing but how often do you expect students to have to remember, oh wait...is it next century or this century? Just remember that all terms start with 2 and ignore the digit.

  • FuBar (unregistered) in reply to da Doctah
    da Doctah:
    It's not as WTF as those deranged systems where the day comes before the month.
    Deranged?? Excuse me, but what makes sense is either least-significant to most-significant or vice versa, i.e. 05-JUN-2010 or 2010-JUN-05. What's deranged is 6/5/10, which messes up the order of significance. Who thought that one up??
  • (cs) in reply to some anon
    some anon:
    I'm surprised no one has yet mentioned the huge SQL injection vuln here: any and all queries are probably created dynamically.

    Anybody want an A+ in their algos class? :)

    Dynamic queries don't create a SQL injection vulnerability, unsanitized user input does. There's no reason to think that they don't fully sanitize the term when it comes from the user, if it comes from the user.

  • some anon (unregistered) in reply to Markp

    Ever heard of the term canonicalization? Unless they're using prepared statements or stored procs (impossible in this scenario), the possibility of SQLi is there... The problem is that there is no separation of data and code (ergo, SQL injection).

    EDIT: I spoke too soon, unless they're using a VERY restrictive white-list (aka "known good") validation.

  • (cs) in reply to Markp
    Markp:
    some anon:
    I'm surprised no one has yet mentioned the huge SQL injection vuln here: any and all queries are probably created dynamically.

    Anybody want an A+ in their algos class? :)

    Dynamic queries don't create a SQL injection vulnerability, unsanitized user input does. There's no reason to think that they don't fully sanitize the term when it comes from the user, if it comes from the user.

    Yup, and numbers are really easy to santize. I also wouldn't be surprised if they had one big procedure that runs one of 281 hard-coded queries based on input parameters. That would allow them to add new features each year without having to back port them to historical data. Sounds very "enterprisey" -- no dynamic SQL, all stored procedures, yet still a big steaming pile of code.

  • some anon (unregistered) in reply to Markp
    Markp:
    some anon:
    I'm surprised no one has yet mentioned the huge SQL injection vuln here: any and all queries are probably created dynamically.

    Anybody want an A+ in their algos class? :)

    Dynamic queries don't create a SQL injection vulnerability, unsanitized user input does. There's no reason to think that they don't fully sanitize the term when it comes from the user, if it comes from the user.

    Oh, and you're right, dynamic queries (on their own) don't create SQLi vulns, but they sure as hell open the door, and force the attacker to be a bit more creative with their attacks.

  • (cs) in reply to some anon
    some anon:
    Ever heard of the term canonicalization? Unless they're using prepared statements or stored procs (impossible in this scenario), the possibility of SQLi is there... The problem is that there is no separation of data and code (ergo, SQL injection).

    EDIT: I spoke too soon, unless they're using a VERY restrictive white-list (aka "known good") validation.

    Stored procs are easy in this scenario. See my above post. Also, it is pretty easy to make a white-list for numeric input that is 100% injection proof. As long as the input is composed only of the digits zero through nine, the input is safe.

  • some anon (unregistered) in reply to Jaime

    You're right, it's just that "solutions" like this always rub me the wrong way... If you have to jump through some serious, clumsy, and awkward hoops, you're doing it wrong. Doing junk like this (and even this kind of denormalization is very wrong as pointed out by many other posters) is for data warehousing scenarios, not for a living application that's sitting on top of it, IMHO.

  • Herby (unregistered)

    Having "2" for the 21st century may be a holdover to when the university was founded. They might have used "0" for the century the university was founded (in the 1800's), and "1" for last century (1900's). It is as good a system as any. The real problem is only having a single digit for the "month term starts". That rules out October, November, and December (not that any term begins on those months).

    Maybe they should use letters to determine the month starting of the term. Then they could use sane letters (A-Jan, B-Feb, ... K-Nov, L-Dec). While I don't remember the letters used for futures trading (it is the last letter of the contract ticker symbol) they bear no sane relation to the month. They use TWO different sets so the contracts can be almost 24 months in the future (but they rarely are!). Crazy!!

  • Todd Lewis (unregistered) in reply to ThomasP
    ThomasP:
    It's not the worst numbering scheme I've ever seen... how would you have done it?

    We're just moving off of a precursor of Banner onto PeopleSoft, but they way it worked in the old system was "YYYYt" where the "YYYY" was the year, and the "t" was the term, specifically:

    2=Spring 3=1st Summer Session 4=2nd Summer Session 9=Fall

    So 20104 is the 2nd Summer Session of 2010. Not crazy, but not exactly sane either. At least it sorts.

Leave a comment on “Database Abnormalization 101”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article