Relational databases were at one time an absolute standard that nearly any developer could be expected to be familiar with. These days, it's still valuable knowledge, but it's less surprising when someone doesn't know more than the basics- there are so many other ways one might persist data, each with their own tradeoffs. But if someone is developer for a contracting firm specializing in data driven applications, you would expect them to have a little more grounding in RDBMSes than the average developer. Well, you would expect that if you thought the contracting firm was actually selling a valuable service, instead of trying to bilk their customers and their contractors and hope nobody notices.
The important things about RDBMSes is that each table represents a relation- a set of fields that are all conceptually related, like "Employee" (with an employee ID, a name, a hire date, etc.) or "SalesListing" (with a product description, a price, etc.), and then those tables can have relationships to other tables- foreign keys that guarantee that if "SalesListing" has a "SalesRep" field on it, that "SalesRep" has a matching entry in the "Employee" table.
Roman received some SQL code from one such contracting firm that perhaps highlights where these sorts of things might go wrong:
ALTER TABLE [dbo].[ListingDetails] WITH CHECK
ADD CONSTRAINT [FK_Listings_Listings] FOREIGN KEY([Id])
REFERENCES [dbo].[ListingDetails] ([Id])
GO
The ListingDetails table has a primary key, Id. This code adds a foreign key constraint on that column, referencing… that column. It's a foreign key back to itself, which guarantees that for every Id in ListingDetails, there is a matching Id in ListingDetails. This is a pretty easy constraint to enforce, and while it's stupid, it's likely pretty harmless in the scheme of things. But it certainly represents a certain level of attention to detail and design competency in the schema created by this contracting firm.
[Advertisement]
Utilize BuildMaster to release your software with confidence, at the pace your business demands. Download today!
This post originally appeared on The Daily WTF.