Inner Drive Software

I've been helping a client get a custom database application working for a while. The previous vendor never quite completed it, then got testy when the client brought me in. When the vendor told them "either he goes or we go," my client said "see ya." Fortunately the client got me the entire code base, and as their de facto network administrator I already had dbo access to the database.

There are two unbelievably bad things about the vendor's data design that I want to share. First, they created a table called "passwords" that has user passwords in plain text. I would expect that even non-programmers of reasonable intelligence can intuitively grasp why this horrifies me, but apparently the vendor's crack team of coders had no problem with this design. It horrified my client when I casually connected to the database using a Microsoft Access and executed the query "select * from passwords".

The second problem is more subtle, in the way that spilling hot coffee on yourself would be a more subtle effect of a rear-end auto collision than the crunching metal and plastic part of it. Throughout their database, instead of using int primary keys as most people do, they concocted this method of creating row IDs based on some piece of data in the row followed by a four-digit number. For example, the accounts table might have an entry for "Inner Drive Technology" whose row ID would be something like "Inne1024". And they leave it up to the ASP.NET code to create the IDs. And none of the ID columns has a UNIQUE constraint on it.

Can you think why the application sometimes has trouble retrieving data?

Because I'm in triage mode right now, I don't have time to design a proper database, but I at least created these objects to minimize the likelihood that inserts will fail:

CREATE TABLE next_id (
	last_id	int	NOT NULL
)
GO
INSERT next_id (last_id) VALUES (2000)
GO
GRANT SELECT, UPDATE ON next_id TO PUBLIC
GO
CREATE PROC GetNextId
	@TextValue varchar(512),
	@Result ACCOUNT_ID OUTPUT
AS BEGIN
	DECLARE @FirstFour char(4)
	SET @FirstFour = SUBSTRING(RTRIM(@TextValue) + '000', 1, 4)

	BEGIN TRANSACTION
		DECLARE @LastId int
		SET @LastId = (SELECT MAX(last_id) FROM next_id)

		DECLARE @NextId int
		SET @NextId = @LastId + 1

		SET @Result = (@FirstFour + '' + CAST(@NextId as varchar) ) 
		
		UPDATE next_id SET last_id = @NextId WHERE last_id = @LastId
	COMMIT
END
GO
GRANT EXEC ON GetNextId TO PUBLIC
GO

It's what my wife would call a "Hoosier" solution, but at least it guarantees that row IDs are, in fact, unique.