Oracle for Absolute Beginners: Part 8 – Database Design and Normalization

A wise man once said, I know one thing: that I know nothing. If you’ve followed this series from the very beginning I am hoping that you’ve gone from knowing absolutely nothing about Oracle database programming to this point where you know the foundations, know how the parts fit together – basically, know enough to know that there’s a whole

A wise man once said, I know one thing: that I know nothing.

If you’ve followed this series from the very beginning I am hoping that you’ve gone from knowing absolutely nothing about Oracle database programming to this point where you know the foundations, know how the parts fit together – basically, know enough to know that there’s a whole lot more that you don’t yet know.  Welcome to my world; I’ve been doing this for 15 years and I still feel there’s a lot to learn.

In this final instalment we will talk briefly about database design, tie up some loose ends, and I will act the role of the wise old man and bore you with some advice based on those 15 years of mine.

Database Design

Before I became a software developer, I worked as a shoemaker (this is a total lie; but I’m just trying to make a point). I once got an order to make 3 high-heeled shoes for Jennifer Aniston  (since this is a lie, it might as well be a big one). And I only had 2 days! Not enough time!

I realised that I could either use one day to design the shoes and risk only making 2 pairs; or I could work without planning and deliver all 3 pairs – except Jennifer Aniston will have to wear shoes with the right heel shorter than the left to the Oscars.

The moral of that story is – obviously – that you should spend as much time as you can getting the design of your database just right. Poorly-designed databases are harder to write code for and often run queries inefficiently; and since databases often survive decades, those costs pile up astronomically over the years.

Normalization

There is a process called normalization that supplies us with rules which help us design efficient tables.

  1. First Normal Form: An entity is 1NF when it contains no repeating groups of data.

What this means is that, using our FRIEND_NAME table as an example, no record in the table should contain more than one of the same kind of data. You shouldn’t record both Joey Tribiani and Chandler Bing in the same record, even though they live together. They must have separate, individual records.

  1. Second Normal Form: An entity is 2NF when no records contain non-key attributes that are not dependent on the primary key and apply to multiple records.

What this means, basically, is that your FRIEND_NAME records must not contain address details. Firstly, the address is not dependent on the person (since they can move); secondly, multiple people can live at one address and we do not want to end up repeating those address details in our record for Joey and our record for Chandler. The correct thing to do with such data is to move it into a table of its own, an ADDRESS table.

  1. Third Normal Form: An entity is 3NF when no records contain key attributes that are not dependent on the primary key.

An example might help here. Imagine we wanted to know how many housemates each of our friends had. If we add a column to FRIEND_NAME named housemate_count our table will not be 3NF. This is because housemate_count is not wholly dependent on our primary key

There are many, many books repeating the rules of normalization (which is ironic, since normalization is all about not repeating stuff), and there’s no way I can explain the subject fully here. But if you can remember my 3 rules of database design, you’re on to a good start.

  1. The world is made up of things. Create tables for your various things – friend, address, phone number, email address   and never have two different types of thing in the same table.
  2. Things often interact with other things (or themselves). Create tables to record these interactions – FRIEND_PHONE, FRIEND_ADDRESS, FRIEND_EMAIL. Never have two different types of interactions in the same table.
  3. Things and interactions sometimes need descriptions. Create tables to record these descriptions. (If, for example, our ADDRESS table had a column named address_type which could be ‘apartment’, ‘townhouse’ , ‘bungalow’, then address type is a description of our thing, and should be in a separate lookup table.)

And there you have it: things, interactions, descriptions – my 3 rules of database design. There’s obviously more reading for you to do, but this is a good start.

Further reading

Now that I think of it, while this series has, hopefully, given you a solid foundation, there are some important omissions that you might want as the starting point for your further reading: the dual table, case statements, the timestamp data type, packages.

There is always more to learn, more to study. It’s like that scene in the movie Kill Bill where every time she beat up 5 bad guys, 10 more would turn up. And, if we’re being honest – about Oracle, not about Kill Bill – you probably aren’t going to remember everything I’ve told you this far.

But if you don’t know all there is to know, and you don’t even remember the things that you do know, what makes (or will make) you a good Oracle programmer?

Thank you for asking: I’ve got 3 rules.

Three rules every programmer should remember

1. Your best programming should be done before your coding starts.

The more time you spend planning, thinking, researching, the better your eventual code will be. Coding is exciting and the temptation will be to dive straight in. Resist it.

2. Clever rewards you now; boring rewards you later.

When writing procedures, functions, triggers, producing the code is the interesting work, the clever work. Commenting, instrumenting and formatting that code is the boring work that you probably do not want to do. But believe me, when you return a year later to maintain your code you won’t be cooing over how clever your code is, you’ll be cursing yourself for not commenting enough.

3. Cleverer is not necessarily better.

Or, as my computer programming lecturer once said: never write one line of code where you can write two.

PL/SQL is a beautiful language and, with each release, Oracle buttress it with new functionality and clever ways to do stuff. But I would advise against being seduced by the clever new function that you’ve just learned unless it is definitely more efficient and as easy to understand as what it is replacing. The two things we always strive for with our code must be efficiency and legibility. For example, at work, I rarely use the NULLIF function because my colleagues are not all that familiar with it (and I need them to be able to maintain my code); instead I would use a much longer case statement. Not necessarily as clever, but definitely better.

Conclusion

And those, my friend, are the foundations of Oracle database programming from tables through SQL all the way to PL/SQL. As I have said often, there is more to know, but everything new you learn will be no more than an extension to the lessons in these 8 articles. And that’s exciting.

So there’s only one way I can end this series. With these words:

THE BEGINNING.