Let’s say you have a FOO table that admins and users can update. Most of the time you can write queries against the FOO table. Happy days. Then, I would create a FOO_HISTORY table. This has all the columns of the FOO table. The primary key is the same as FOO plus a RevisionNumber column. … Read more
What do you think about having a related translation table for each translatable table? CREATE TABLE T_PRODUCT (pr_id int, PRICE NUMBER(18, 2)) CREATE TABLE T_PRODUCT_tr (pr_id INT FK, languagecode varchar, pr_name text, pr_descr text) This way if you have multiple translatable column it would only require a single join to get it + since you … Read more
Storing “Simple” Repeating Patterns For my PHP/MySQL based calendar, I wanted to store repeating/recurring event information as efficiently as possibly. I didn’t want to have a large number of rows, and I wanted to easily lookup all events that would take place on a specific date. The method below is great at storing repeating information … Read more
If the latitude coordinate is reported as -6.3572375290155 or -63.572375290155 in decimal degrees then you could round-off and store up to 6 decimal places for 10 cm (or 0.1 meters) precision. If the coordinate reference system (CRS) is not EPSG:4326 (e.g., EPSG:3857) then the x and y values measure a distance in meters from the … Read more