I am by no means an expert DBA, but the design mentality behind a product that my company uses leaves me scratching my head. This product which shall go unnamed (Exact JobBOSS) has a variety of faults, but none more perplexing than their ‘double keying’ of tables.
Now I’m not talking about a compound key here. No, this is where they’ve gone through and put the key in twice, such as this example from the ‘Address’ table:
The logic behind this design decision is somewhat puzzling. I guess they figure that a user may want, for example, a new customer to have an old customer’s Address number? Not only does that not make any sense on it’s face, but A) The user has no control over how this number is assigned and (the kicker) B) This second key is allowed to be null within the database. Only the application rules keep a catastrophic event from occurring in making sure that this second ‘key’ is not null.
Now you might be wondering: what’s the big deal if this second key is null, after all, there’s a primary key on the table already. Here we get to square the circle of this puzzling design decision because as it turns out other tables do not ‘key up’ to the primary key of the table, but the second, fake, ‘key’. Here’s the create code from a table that ‘keys up’ to the Address table (emphasis mine):
ALTER TABLE [dbo].[RptPref] WITH NOCHECK ADD CONSTRAINT [FK_Addr_on_RptP] FOREIGN KEY([Address]) REFERENCES [dbo].[Address] ([Address])
Not [Address].[AddressKey], but [Address].[Address]?!?* Obviously there’s two issues that immediately spring to mind in such a situation, both of which the developer is attempting to control strictly through their Franken-code. The first is, like I stated above, the field can be null. What if this record is not inserted properly and it is null? Fun times I’m sure!
The second issue is that since the field is not a key, there can be more than one record with that same value. Is their code tight enough to prevent this possible corruption issue? Like much to do with this product, you’ll have to take it on trust!
Addendum: I should also point out a basic programmatic issue with this whole design methodology. If I want to modify records in a table which contains a column that is referenced as a key in a different table, but isn’t a key in the source table that I want to modify, there’s no (easy) way to catch this. In the back of my mind, I’m thinking that maybe the database was designed as a complicated mess in order to make it semi-proprietary.
*(I should point out that the database for this product is small enough that it’s feasible to make a (tiny) entity chart of it within Visual Studio; but beware that easily half the tables will appear to have no relationship with any other table because of situations like the above (‘keying up’ to a possible null non-key, that’s actually never supposed to be null)).
**(While I ranting about JobBOSS I figured I’d bring to light the package’s great love of the ‘rtl70.bpl access violation’ errors that it is prone to getting. I long thought that it was an issue with my installation, but I’m now convinced that they’ve coded the Delphi portions with little to no error handling (is there no ‘try/catch’ in Delphi?)).