Why using Database Ids is not recommended for filtering data in our codes

Why using Database Ids is not recommended for filtering data in our codes

Database Ids are used for internal purposes by the database as primary key, foreign key, and/or indexing. Ideally, we don’t want to display this column in our applications. As a result, we use models to map the relevant data.

For example, a student table might contain:

Id, studentCode, firstName, lastName, gender, address, dateOfBirth, course, startDate, endDate.

Assume we want to display a list of students but won’t display the Id. We also don’t want to display the dateOfBirth due of privacy.

We create a model to map the data from the database and return a list either through an API or if it’s a web application, a page with the table.

For some reasons, let’s say a couple of students freeze their courses to resume later. We have a requirement that they should not be included in the list. The best approach in my opinion is having a field named status with enum values such as active, completed or freeze. The database administrators update this field respectively. We then just need to filter by status to exclude them.

But let’s assume like most real world applications, we don’t have access rights to modify the database structure. We can only change our backend codes.

For a start, as we have these student data, we can filter them by their Id. This will work returning only the active students list.

However, this approach might not be a good practice because if the applications use multiple database, one for development, acceptance and/or production, hard coding the IDs is a bad solution. The other databases won't have the same internal values unless they are direct copies.

A better solution is to have a constant field, irrespective of databases, such as studentCode that is unique and does not change.

Id: 112341

Code: Kaz3113

We can have a list of student codes that we compare from the list of students and exclude them respectively before sending back the data to the API or frontend.