If I am to store different types of sensitive data, let's say: field1 in Table A and field2 in Table B and the data is unrelated, should I use a different key for each field?
Add information 1: The encryption/decryption is going to take place in the application code and the database will only store the encrypted data.
Add information 2: Right now, I'm planning to use AES-256-CBC and the database is MongoDB.
Based on the information provided in the update, I would suggest to use the same key for each field. Different keys would be an overkill as you might want to encrypt other fields as well and also it would be cumbersome to map the fields with different keys.
Following points can be considered with this approach :
Edit 1 :
It depends.
In general, the primary reason for using different keys is: you might want to supply different parts of the data to different subsystems. If there are some columns processed by a system A and some columns processed by a system B, you might want to use different keys for these groups of columns. (This consideration may also apply if one subsystem needs access to all the columns and another to only some.)
If, however, all processing happens within a single subsystem — that is to say, all the keys are distributed together and all parts of the data are always decrypted together —, managing separate keys will not be very beneficial at all in terms of confidentiality maintenance, and near-zero benefits will probably be outweighed by the costs of extra complexity.
So, this is the space axis. However, depending on your context, you might also need to consider the time axis, whereby the encryption keys may change over time, and/or data encrypted with different keys may need to be held in same columns. For example, a combination of a policy of regularly changing the encryption key and the costs of decrypting and re-encrypting a large set of data may necessitate protecting the accessibility aspect of data security by performing the re-encryption process by small steps, which means that during changeover, your system may encounter both data encrypted with the soon-to-expire old key and data encrypted with the recently-issued new one.
Coming back to what you asked — it is pretty unusual for a structured database to not be trusted to have access to data in it, so there are no established best practices for this kind of situations. It is more common to run the whole database on an encrypted data storage, usually in the form of a file system of some sort, or alternatively treat the database as a kind of block device, just serving opaque encrypted blobs whose whole meaning will then be determined in the part of the system which you trust enough to have the cryptographic keys. Analyse, for example, what kind of potential attacks you want to protect your system against. If you are concerned that an attacker may be able to read data out of your database, should you also be concerned that the attacker may be able to insert bogus data, possibly data he might not understand, into your database, or replace good data with bad data or garbage?
