Oh, there's plenty more where that came from. ;)
Oh, there's plenty more where that came from. ;)
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)
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.