Icons: SQL DDL and DML

I’ve been playing around with some icon ideas for SQL Data Definition Language (DDL) and SQL Data Manipulation Language (DML) for some time. Perhaps they will give you some ideas.

First are the Basic DML Commands:

Now let’s apply them to Tables:

Tables need Primary Keys:

They may also need Unique Indexes:

We may have to play with the Columns:

Then we should have Views:

And in special cases Triggers:

We should also define Users:

Now we have all the DDL.

Let’s look at the DML:

Having manipulated our data we should either Commit or Rollback:

And that is about how far I’ve come.

Advertisements

Surrogate Key Best Practice

Surrogate keys (unintelligent keys) are an excellent way to preserve the referential integrity of your database and avoid the need for cascading updates of natural keys if they are changed. However, simply using surrogate key is not always enough.

One of the most common best practices for databases designed with surrogate keys is: Any independent table with a surrogate primary key should have a corresponding natural key with uniqueness enforced. When uniqueness is not enforced duplicate rows are possible and your referential integrity is lost.

For example, consider the table below:

student.jpg

In the student table the primary key is a surrogate key, but uniqueness is enforced stringently by protecting the full name of the student and his birthdate as a unique composite natural key.

Another context for protecting surrogate key referential integrity is by protecting the uniqueness of foreign key combinations where uniqueness would be enforced if the table were dependent. For example consider the tables below:

enrollment1.jpg

Note how the enrollment table has a surrogate primary key, however uniqueness is protected by the unique natural key index on the two foreign keys.

If you use surrogate keys and natural keys together when you design your database, you will avoid the pitfalls of duplicate rows in your data.