database-design

MySQL ENUM type vs join tables

by

Changing the set of values in an ENUM requires an ALTER TABLE which might cause a table restructure — an incredibly expensive operation (the table restructure doesn’t happen if you simply add one new value to the end of the ENUM definition, but if you delete one, or change the order, it does a table … Read more

Derived account balance vs stored account balance for a simple bank account?

by

Preface There is an objective truth: Audit requirements. Additionally, when dealing with public funds, there is Legislature that must be complied with. You don’t have to implement the full accounting requirement, you can implement just the parts that you need. Conversely, it would be ill-advised to implement something other than the standard accounting requirement (the … Read more

PostgreSQL Index on JSON

by

Your other two indexes won’t work simply because the ->> operator returns text, while you obviously have the jsonb gin operator classes in mind. Note that you only mention json, but you actually need jsonb for advanced indexing capabilities. To work out the best indexing strategy, you’d have to define more closely which queries to … Read more

Composite primary keys versus unique object ID field

by

Most of the commonly used engines (MS SQL Server, Oracle, DB2, MySQL, etc.) would not experience noticeable issues using a surrogate key system. Some may even experience a performance boost from the use of a surrogate, but performance issues are highly platform-specific. In general terms, the natural key (and by extension, composite key) verses surrogate … Read more

In what way does denormalization improve database performance?

by

Denormalization is generally used to either: Avoid a certain number of queries Remove some joins The basic idea of denormalization is that you’ll add redundant data, or group some, to be able to get those data more easily — at a smaller cost; which is better for performances. A quick examples? Consider a “Posts” and … Read more