Skip to content

Columns to remember for your SQL tables

When learning SQL language, you quickly learn that a primary key with a unique identifier is very important to recognize a row and modify it more easily. With more experience we learn the usefulness of other equally important columns to implement. This article presents columns that you should think about implementing so as not to regret it later.

Modeling the fictitious data of a user table

Why are you interested in these columns now?

Forgetting to record an information as important as the date of addition of the line can cause great problems to know for example when a user has registered or at what time there was an attempt to hack data. Some data must be planned as soon as possible because it cannot be added retroactively.

List of important data

Here is a non-exhaustive list of some data that it is wise to save in most of the tables of a database:

  • A unique identifier, often called “ID”, which serves as the primary key (cf. PRIMARY KEY)
  • A random security-related element, such as a “hash”, “salt” or “key”. It can be a random string encoded in MD5 (or SHA1) for example to be used in addition to the unique identifier to avoid CSRF (Cross-Site Request Forgery) flaws.
  • The information when adding the line:
    • The date of addition in the form of a local DATETIME or GMT or a TIMESTAMP
    • If applicable: a foreign key (cf. FOREIGN KEY) linked to the account or to the user who added the data
    • If applicable: the IP address of the person adding the information
    • If applicable: the hostname of this IP address
    • If applicable: the user agent
    • If applicable: details to find out if the line has been modified from the user interface, the administrator interface, a CRON or any other interface
  • The same information when the row was last modified
  • A private comment intended to remember details about this line
  • Information on whether the line is active or if it has been deleted, such as “is_active” or “is_deleted”. This keeps the data while artificially deleting it in the eyes of users by filtering on this value

Of course all this is specific to each project and depends on the use of each table. You should know that some projects will not need to log so much information. For example, a banking application will have to record as much information as possible to secure the application as much as possible, while a small, unimportant project will not have the utility of recording the user agent or the user hostname. who make changes.

This list is quite personal and can be enriched with your knowledge and good practices that you have the opportunity to do in your projects using a database. Comments are there to share your experience.