A relational database? What's that?



  • This is my first post to the daily wtf forums.... 

    I've been lurking for a while, and after seeing dozens of my own WTF's where I work at, I figured I'd join in and share.

    When I first joined up with GlobalMegaCorp (as I will call them), I took a look at their relational database to get familiar with their data, and how it relates to itself.
    "Relational database", as it turns out here, is just a buzz word.   Primary and Foreign Keys (or lack thereof) asside, I discovered a very clever way that new primary keys are generated:

    Protected Function GetContactID() As Long
            Dim ContactID As String = Nothing

            con = New SqlConnection(strConn)
            con.Open()

            Dim rndNum As String
            Dim rndObj As New Random
            'get random number between 1 and 99,999
            rndNum = rndObj.Next(1, 99999).ToString

            sql = "INSERT INTO tblContactID (EmployeeID) Values( '" & rndNum & "') "
            cmd = New SqlCommand(sql, con)
            cmd.ExecuteNonQuery()

            sql = "Select Counter from tblContactID Where EmployeeID = '" & rndNum & "' order by Counter Desc"
            cmd = New SqlCommand(sql, con)
            reader = cmd.ExecuteReader()
            If reader.Read Then
                ContactID = reader("Counter").ToString
            End If
            reader.Close()

            'Delete seed record from tblOrderID
            sql = "Delete from tblContactID Where EmployeeID = '" & rndNum & "'"
            cmd = New SqlCommand(sql, con)
            cmd.ExecuteNonQuery()
            con.Close()

            Return ContactID

    End Function

    That's right, for tblContacts, there is a table called tblContactID. 
    A random number is generated and inserted in to the "EmployeeID" column of tblContactID.  After the record is inserted, a query is made to select the "Counter" field from the table using the random number, or "EmployeeID" (Making sure to carefully sort desc, in case another random number (of the same value) was inserted during this short period). 
    The "Counter" field, just as it describes, is an identity, with an increment of one.  This new number is retrieved from the tblContactID table, and the record that was just inserted in to it is quickly deleted.  This new, unique number is then later used as the primary key of the tblContacts table when that record is later inserted.

     



  • While you're correct that this is a rather, umm, "interesting" way of using the db to generate surrogate keys, I can't resist pointing out that TRWTF is that you think that "relational" in "relational database" has something to do with foreign keys.

     

    - rel

     



  • Yeah, I started this post with a different idea.  I should have changed the subject when I had a chance.

    And, yes, I realize the "relational" part of it has more to do than just foreign keys.  (The first WTF I had with this database is noticing that it had neither PKs nor FKs anywhere)



  • @rel said:

    I can't resist pointing out that TRWTF is that you think that "relational" in "relational database" has something to do with foreign keys.

    Well, if they're foreigners, then they can't be related!  Duh!

     



  • @rel said:

    TRWTF is that you think that "relational" in "relational database" has something to do with foreign keys.
     

    It may not be part of the formal definition, but in this day and age, if you're not enforcing referential integrity then you might as well be using flat files.

     

    On topic: What I particularly like is how, instead of using an actual transaction scope, the programmer sloppily tries to compensate for conflicts by sorting.  Of course, in the case of an outright failure, which will happen eventually, there will be records with random numbers sitting around in a critical table.



  • @CitizenBane said:

    Yeah, I started this post with a different idea.  I should have changed the subject when I had a chance.

    And, yes, I realize the "relational" part of it has more to do than just foreign keys.  (The first WTF I had with this database is noticing that it had neither PKs nor FKs anywhere)

     It's the content of the post, not the subject title, that I was pointing out. See:

    "Relational database", as it turns out here, is just a buzz word.   Primary and Foreign Keys (or lack thereof) asside...

    That makes it look like you think that the ability to declare relationships between tables is why we call it the relational model. But that's not why it's called the relational model, nor why we refer to implementations as "relational databases".

    Not a real big deal, though. Most data professionals have the same misconception.

    -rel



  • For what it's worth, I think it is an excellent WTF and a good candidate for "from the sidebar".  A nice, short, self-contained function and it's an new, original "wtf anti-pattern" as far as I can tell ... well done!



  • @rel said:

    That makes it look like you think that the ability to declare relationships between tables is why we call it the relational model. But that's not why it's called the relational model, nor why we refer to implementations as "relational databases".

     

    Because your not coming right out and saying it.  Your refering to relational algebra in comparison to what a relational database is right?

    If that's the case then I think your probably being a pedant. Sure FKs and PKs are not a part of relational algebra they really really help with the simplification of implimenetaton.  I think the beauty of this abstraction is someone can use relational principles without a need to fully understand the math.Not everyone who programs has learned set theory.

    Additionally what's a 'Data Professional'?



  • Oh, there's plenty more where that came from. ;)

     

     



  •  /me copies function... pastes into personal code library.



  • @flaxeater said:

    @rel said:

    That makes it look like you think that the ability to declare relationships between tables is why we call it the relational model. But that's not why it's called the relational model, nor why we refer to implementations as "relational databases".

     Because your not coming right out and saying it.  Your refering to relational algebra in comparison to what a relational database is right?

     

    You don't really need to go that far. A relational databse is called that because each table forms a relation (in the mathematically defined sense of a set of n-tuples). This is different from the older hierarchical databases. Of course, in this sense, a collection of flat files is a relational database as well...


Log in to reply