Friday, April 3, 2009

Web database design

I believe most of general datbase design principles apply to web database design as well. However, i also believe web database design should have some specific. No matter all, database design is an old subject in computer science while web application, compared with database, is a pretty new area.

I think the biggest headache to web developers is finding a balance between redundancy and normalization, etc, speed and space. Redundancy can minimize the database query times and the need to join tables. However, this obviously violates the 3rd normalization principle, the school theory of database design.

Personally, i don't mind to have some redundancy in my database. But there is one rule: only the redundent information can let you uniquely identify a row, which means, with that redundent field, you can have construct a unique key for the table.

Let's explain with an example. Suppose we have mobile subscription services. Each service have a shortcode. To subscribe these services, a user can send a signup keyword to a shortcode. Obviously, shortcode and signup keyword must be unique. However, one service can have variety of signup keywords.

The shortcode table may be like this
id, shortcode, country_id

The campaign table is
id, name, shortcode_id

The signup keyword table is
id, keyword, campaign_id

Now if a user send a keywork to a shortcode, i need to find out which campaign he is going to subscribe. Based on the above tables, i cannot uniquely identify a keyword, and then find out the campaign. In this situation, i don't mind adding a shortcode_id to the keyword table, so that with keyword and shortcode, we can uniquely identify the keyword, and then identify the campaign.