Personal Workflow Blog

To content | To menu | To search

database

Entries feed

Tuesday, 8 September 2009

Databases: Efficient Case-insensitive searches with Function-based Indexing

Doing a case insensitive search is a very common task, but is quite hard to optimize correctly. But since it's done via a UPPER(MY_COLUMN) = UPPER('MY_DATA'), it doesn't use the index that could be on MY_COLUMN.

Different RDMS means different approaches.

Continue reading...

Friday, 31 July 2009

Databases: Better Defer Constraints than Avoid Them

Constraints are considered a Good Thing. They enable to rely more heavily on the validity of the database. It is quite important to note that validity is in terms of modeling and not in terms of business [1].

The biggest complains we can have about constraints is that it is sometimes quite annoying to do some updates while consistently validating the constraints. You have to care about the order of the operations you do.

Notes

[1] I'll write an article about this later

Continue reading...

Tuesday, 2 June 2009

Databases: Efficient Denormalization with Views

Everyone is unanimous that database normalization is considered a Good Thing.

But it usually comes with a cost : writing queries can be very tedious since you always have to join many tables together to be able to retrieve useful human data from all those reference tables.

Continue reading...

Tuesday, 19 May 2009

Surrogate Keys : Globally Unique, Application Unique or Type Unique ?

When you just decided to use Surrogate Keys, another problem arises : which value should I use ? It seems to be a very simple problem since the key now is completely in isolation in your application and is not related to any data. The choice is quite bonding, since a change means that every value has to be changed, and you did not let this key leak outside the database did you ?

The keypoint here is isolation. Many differents kinds of isolation are available, and soon you will be able to make a informed decision.

Continue reading...

Friday, 17 April 2009

Databases: Partial Indexing

In huge tables, usually only a fraction of the table is used on a regular basis. The rest is useful either for historical purpose, or is just not being treated right now.

Continue reading...

- page 1 of 2