Databases are funny things, you can use them as object stores, but the set theory math behind them makes them powerful concepts.
I extensively use the open source Postgresql Database System. It offers a free environment with a complete ACID compliant relational system. The more popular MySQL offers that functionality in bolt on modules, but the speed advantages are quickly lost there.
Basic Tips for PostgreSQL Users:
- EXPLAIN, EXPLAIN, EXPLAIN: Look at your queries, see what doesn’t use an index
- LOG, LOG, LOG: Find out where your database spends time, optizmize those queries
- CASCADE: If you have a foreign key relationship, set what happens if you delete the target, Set Null or Delete, don’t just prevent the deletion and require the user (software) to delete first
- pl/pgSQL: Learn it, triggers are powerful things that you can use to track your data and maintain summary tables (much faster than counts), or streamlined version. BEFORE INSERT and BEFORE UPDATE triggers let you verify, clean up, or otherwise restructure your data on the way in, very useful. AFTER INSERT and AFTER UPDATE triggers let you update other tables, or move data through a system without ugly batch processes.
- Hardware: RAID 10 is your friend, RAID 5 is your enemy, drive space is cheap, 4 drives isn’t much more than 3.