database
Naming conventions for DB triggers
For sorting reasons I would recommend a structured approach: TR_Users_AD TR_Users_BD TR_OtherTable_AI and so on. In the end it’s up to you – but whatever you choose to do, stay consistent, at least within the same database.
How to execute an Oracle stored procedure via a database link
The syntax is EXEC mySchema.myPackage.myProcedure@myRemoteDB( ‘someParameter’ );
Redis notifications: Get key and value on expiration
The feature that Eli linked to allows you to listen when a key expires. However, it does not give you the value of the key. Futhermore, based on the filed github issue it does not look like you can expect to have this feature built in anytime soon if ever. The solution I use is … Read more
Best way to store enum values in database – String or Int
The implementation is easy in both cases, and performance differences should be minor. Therefore, go for the meaning : the Strings are more meaningful than numbers, so use the String.
One DB per developer or not?
If you all share the same database, you might have some issues if someone make a structure change to the database and that the code is not “Synchronized” with it. I highly recommend one DB per developer for the only reason that you don’t want to do “write” test to see someone else override you … Read more
How to represent a tree like structure in a db
I showed a solution similar to your nodes & edges tables, in my answer to the StackOverflow question: What is the most efficient/elegant way to parse a flat table into a tree? I call this solution “Closure Table”. I did a presentation on different methods of storing and using trees in SQL, Models for Hierarchical … Read more
How to store timestamp with milliseconds in PostgreSQL?
I know this is an old question, but I had a similar problem. You can use the timestamp(n) field, with n equals the number of digits of precision to store on the date (timestamp(3) would give you millisecond precision) Example CREATE TABLE date_test (datetime timestamp(3) with time zone); insert into date_test values(to_timestamp(1525745241.879)); select EXTRACT(epoch FROM … Read more
Calculate distance between Zip Codes… AND users.
Ok, for starters, you don’t really need to use the Haversine formula here. For large distances where a less accurate formula produces a larger error, your users don’t care if the match is plus or minus a few miles, and for closer distances, the error is very small. There are easier (to calculate) formulas listed … Read more
When should I use Oracle’s Index Organized Table? Or, when shouldn’t I?
Basically an index-organized table is an index without a table. There is a table object which we can find in USER_TABLES but it is just a reference to the underlying index. The index structure matches the table’s projection. So if you have a table whose columns consist of the primary key and at most one … Read more