Monthly Archives: March 2015

Database Normalisation

In March I have been concentrating on learning more about Databases. I got a couple of books (7 Databases in 7 weeks by Pragmatic Programming, and SQL and Relational Theory by CJ Date), I got the Lynda program I spoke of previously. I’ve also been doing some coding tutorials online like Learn SQL the Hard Way and SQL Zoo.

As many of my programming friends, colleagues and mentors predicted – the textbooks were my least favourite approach. In 7 Databases, I could only get through the intro and the chapters on Postgres before my eyes glazed over. The Relational Theory one I hope to read more of in the future, but I couldn’t get into it right now – I’m feeling like learning more practical skills for now, rather than theory. I wanted to know how to understand more about the database I’m using every day at work.

Continue reading

Leave a reply

Database Indexes – The Best Explanation in the World

Every time I think I understand indexes, I later discover I don’t. Now I feel like I REALLY do (take with a grain of salt), and this is how I understand them to be…

Indexes are all about speed of access. While a primary key lets us uniquely identify a row, it doesn’t help us find the row really fast – that’s what an index is for. An index helps you jump directly to the relevant record.

A clustered index is the one column in a database that the data is ordered by. You choose one column to order all the data by down on the disk level where it is saved.

Most often, the primary key on any table is the clustered index. Whichever column is the clustered index, the database will order the data in the table based on that column.

Each table can have only one clustered index, but you can have others which aren’t clustered.

If you query on the clustered index column – the query will be very fast. However, if you query on a column without an index, the Database management system has no idea where that particular record might turn up, so it would require it to read through every row of the database looking for it. this is also known as a Full Table Scan, which is massively inefficient. The more rows, the worse it gets. If you get to 100000s, it is highly inefficient.

So you might do a non-clustered index, or secondary index, on another column. This is like an index in the back of the book.
Continue reading

Leave a reply

Avoid SQL Nightmares

It’s a simple, simple tip:

Instead of being all like

DELETE * FROM users
WHERE id = 23

How about first you go

SELECT * FROM users
WHERE id =23

and once you’ve seen the results and are sure you’ve got it exactly right, then and only then, change SELECT * to DELETE

Leave a reply