The developers of the system are well-trained in application development, but less trained in database design. I have seen this happen in the real world, in situations of the following kind:Īt the time when the system is designed, there is only one application which has exclusive access to the database, so referential constraints and ID columns are not that important at the beginning. The reasons for such design decisions are often not technical ones, but organizational ones. I think this is a maintenance nightmare.Ĭan someone make any reasonable arguments to objectively support the decision this vendor made? I'm having a hard time coming up with any good reason. And if someone had to run some queries directly in the database, that also poses a problem. For instance, I have to save data to this database and I don't have any of their 'referential integrity code' in my app. I'm pretty much convinced they have made a horrendous mistake.

They also said they handle all referential integrity in their code. In following communications with them, they pretty much said that identity columns are the exception, not the norm. It was clear from the schema sent to me that they were not using relations nor were they using identity values for primary keys in most cases. Because of that, they've given me portions of their database schema and descriptions on how to save the data with SQL queries. They have no interface for sending and retrieving data. Both applications use SQL Server as a database. It's a third party application that my application has to interface with. Now that my opinion is out of the way, I'd like some advice on an application we have at work. And in my case, 99.9% of the time I utilize a unique identity column as a primary key. I've always been of the opinion that you should define relations in your database and let your database handle the relational integrity.

I have about 15 years software engineering experience, writing business software with relational databases.

Any free relational database software