On a recent project we had to connect to AWS Aurora postgres 10.6 version of the database in SSL mode using JDBC and Java 11 JRE. When the Aurora cluster is setup, we can force all connections to use SSL by using the options group settings (
forceSSL=true), establishing secure connection from the application to the database is not as easy as it looks.
Many projects need addition of identical columns to all the tables created by the project. Audit columns are an example of such a requirement. The requirement is to add columns such as created_by, created_date, modified_by and modified_date to all the tables, these columns store, who created the row, when the row was created, who modified the row last and when was it modified. created_by and created_date are required to be present when the row is inserted and thus are required to be not…
In every enterprise and every project we end up having multiple environments, especially the database side of the enterprise tends to stick around for a longer period of time and has much more dependencies or application integration as opposed to application urls etc.
Given this, how to name the servers, databases and schemas becomes a very important decision, do these names provide for an easy way to use the application and not make it harder or the developers to access the database.
Most of the time I have seen database foreign key constraints on tables without indexes on those columns. Lets say the application is trying to delete a row from the CUSTOMER table
Some environments like to have access to the database tables routed via stored procedures.
Instead of using Create/Read/Update/Delete (CRUD) with DML, stored procedures are invoked with the parameters to perform the required operation. I'm not arguing about the benefits/pitfalls of this approach, if you have to do stored procedures, here are some things to look at.
When creating a Foreign Key constraint on the database as shown below
In development mode you don't want to worry about which table goes into what Tablespace in production as it complicates development environments. The production DBA's want to have their input and control over deciding what table goes into what Tablespace. To allow for this I used a mapping scheme as shown below.