Denver Colorado Blog

created by James Worthen

Random Entries

Relational SQL Database Guidelines Tips and Tricks

Dec 1st, 2005 - Subscribe
Keywords: 3nf - 3rd Normal Form

Relational SQL Databases Guidelines Tips and Tricks
by FrosT

Introduction

Today I was helping an old friend create some SQL, Structured Query Language. This friend had very little knowledge of Database programming with MySQL. MySQL, a database program used to store, well data, has to be my favorite database program. After generating some table data in 3NF (3rd Normal Form) I explained to him why and what it was I did with the new table SQL structure. In doing this guide and help with him on his SQL Database venture I decided to write this blog entry for those people who want to know a few tricks about SQL Database Programming for Beginners.

First Things First

First things are first, learn SQL. Take a class, read a book, or look for articles online. This entry will not teach you SQL Database Programming, just a few tips and hints. Now that we have that out of the way the first tip to SQL Database Programming is know what you are coding. Figure out the Use Case scenarios (how users will use the application with the SQL Database) and derive a SQL database from there. Perhaps the biggest mistake I have noticed when programming a SQL Database is no Primary Key. When I code my SQL Database I always have a Primary Key for every table that is unique. Even if I can use let's say an ISBN Number for the primary key, I do not. Reasoning, that is how I was taught and it proves to be very reasonable and useful.

Once your first database comes together you will probably see that is how I code SQL databases. So no matter what, always have a Uniquie Primary Key for every table, you will screw yourself later if you do not.

Indexs, Keys and 3NF oh My!

Indexs are essential in any SQL Database. When making an Index one needs to ask themself, "Will I be querying any data by this column/field?" If you can answer yes to that question, make that column/field and Index. Simple as that. Word of warning, do not go overload on Indexs with your SQL Database, it will slow down runtime. Just do the essential columns in your SQL Database Table with indexs. Keys and Primary Keys: There can be only 1 primary key. This is the master key for the SQL Database table. What the Primary Key does for the SQL Database Table is say, hey every single record here will be "unique" as to say. Here is a real world example of using a Primary Key in a SQL Database Table:

Let's say that I have a user table, but I want to allow users to have the same Username. In my SQL Database Table if I do not have a Primary Key set, this will not be possible. But if I have a Primary Key labeled, "userID" any one person can have the username they want. Great feature.

Now that you know an example of a SQL Database Table use of a Primary Key, what is a key? A key is a unique identifier of a table. Let's take that userid example and say we want usernames to also be unique. Well just make the username a key, and viola no more double usernames allows. Simple as that.

Ending Notes

With this breif introduction to 3rd normal form of a Structured Query Language Relational Database Programming, maybe you to can create a fast, easy and reliable database that will last for as long as you last. SQL Database Programming is tough until you get it down solid. Once you have SQL Programming down it is a walk in the park. Remember if you have any questions about SQL Databases, or want help with 3rd Normal Form, just message me. I am always here to give a helping hand.

Comments: 8

Comments:

spawn2u
on December 01st, 2005

SQL is a lovely thing, use it all the time in GIS apps and maps....I often pull information that is related to a spatial feature for reporting from a dbms. My power is now in MS SQLServer, that is what we use but I started with on the AS400 DB2 SQL and worked with Oracle as well.

one time I asked a guy during an interview to write a simple select all statement for the table "tablename"

He wrote "Select ALL From tablename"
I told everyone do not hire this guy but they did and guess what, he was worthless....

Nice little snippet, hope people pay attention the index part, they never do and then they wonder why it is slow or why it slows down over time......
spawn exit

frost
on December 01st, 2005

Yea that was Aeonity's problem for a while. But I figured it out and fixed it. I wouldn't of hired the select all guy what a tard...

Select * from People where clue = none; uh oh overload!!!

--FrosT

cinderella
on December 02nd, 2005

if you looked closely there were boobies. Johnny Depps!

femmeemo
on December 06th, 2005

Apple Pears (or Asian pears if you prefer) taste so much better than a steak. To Veggies and Meat-eaters alike.

Mmm...Apple Pears.

Yeah.

cinderella
on December 06th, 2005

fairy floss is always nice. random but true. just popping in.

julianroos
on December 10th, 2005

I'm dutch, not German!!! You insult me!! No, that's a joke, i come from Holland, that little land next to Germany...

anonymous
on December 20th, 2005

test

anonymous
on July 11th, 2006

All the best we can make of blogs today heh!!We be out deh like no munns business it a wikkid tutorial bredren,me gwy make use of de tips and tricks dem pon yah blog,RESPECT!!!