I was watching Relational Theory for Computer Professionals – C.J. Date and he made this fun remark:
There’s only three databases in the world: there’s departments and employees, there’s courses and students, and there’s suppliers and parts.
One question that always plagues me: should I use 32-bit or 64-bit keys. I can never make a firm decision on this point. But I’ve just done some back of the envelope calculations in thinking about it.
If you issued one identity per second, every second of every day, it would take 136 years to exhaust the keyspace.
If you issued one thousand identities per second, it would take 50 days to exhaust the keyspace.
My copy of MySQL Workbench doesn’t play 100% nice with my MariaDB install, so I checked out the Database Workbench software recommended by MariaDB and in the end I downloaded a trial version. It runs okay under Wine, so gonna check it out.
Reading about Window Functions.
From What’s SAP?:
A basic installation of SAP has 20,000 database tables, 3,000 of which are configuration tables.
This one popped up in my feed today: Things You Should Know About Databases. My favourite quote was this one:
The SQL standard defines 4 standard isolation levels these can and should be configured globally (insidious things can happen if we can’t reliably reason about isolation levels).
It’s so important to know what isolation level is applicable to your connection/transaction.
In his book The Database Relational Model on page 25 Date says:
Here I would just comment–here comes the hindsight once again–that if as suggested earlier the database is to be regarded as a (correct!) logical system, it must never be allowed to contain any inconsistencies, at least from the user’s point of view. In other words, “remedying inconsistencies” needs to be done on an individual statement-by-statement basis (not even on a transaction-by-transaction basis). See reference [52] for further elaboration on this point.
Reference [52] is then:
C. J. Date and Hugh Darwen: Foundation for Object/Relational Databases: The Third Manifesto. Reading, Mass.: Addison-Wesley (1998). A second edition of this book, under the revised title Foundation for Future Database Systems: The Third Manifesto, is due to appear concurrently with the present book.
I think Date did a lot of harm with this comment. The classic example of a transaction is taking $100 from my account and depositing it in your account. So there’s one statement to take the money out, and another statement to put the money in, and after the first statement but before the second statement the database will always go through an invalid state. If RDBMS integrity facilities were on a transaction-by-transaction basis we could actually use them, as they’re not we suffer. Either we need to do more work than necessary or we have to under specify our constraints, or some awkward combination of both.
In Database Design 25 – Surrogate Key and Natural Key the presenter Caleb Curry agrees with my view that if you expose your surrogate keys they become natural keys. See around t=5:00.
Today I discovered the UN Codes for Trade. It includes info about countries and locations, currency, etc.
This popped up on HN today, looks handy: The Connection Strings Reference. Well, I thought it might be handy, but it doesn’t seem to support PHP PDO.