|Showing entries 1 to 12|
Before you start creating database entities, spend some time designing your database to ensure that it is fit for your purpose. The time you invest in this process saves a lot of time and trouble later. Professional database designers fine-tune their design using a process called 'normalization'. The normalization process takes your database design through a number of 'normal forms', which aim to ensure efficient data access, greater query flexibility, and easier maintenance.
For example, the First Normal Form (or '1NF') ensures that all your database columns contain only a single value. A column that contains multiple data values is difficult to access and keep up to date. It also ensures that each table row only represents a single 'real world' item. Like all the other normal forms, this encourages you to split your data across multiple tables, with less rows in[Read more...]
In SQL terminology
A couple of years ago I wrote a post about key/value tables and how they can ruin the day of any honest person that wants to create BI solutions. The obvious advice I gave back then was to not use those tables in the first place if you’re serious about a BI solution. And if you have to, do some denormalization.
However, there are occasions where you need to query a source system and get some report going on them. Let’s take a look at an example :
mysql> select * from person; +----+-------+----------+ | id | name | lastname | +----+-------+----------+ | 1 | Lex | Luthor | | 2 | Clark | Kent | | 3 | Lois | Lane | +----+-------+----------+ 3 rows in set (0.00 sec) mysql> select * from person_attribute; +----+-----------+---------------+------------+ | id | person_id | attr_key[Read more...]
At Kscope this year, I attended a half day in-depth session entitled Data Warehousing Performance Best Practices, given by Maria Colgan of Oracle. My impression, which was confirmed by folks in the Oracle world, is that she knows her way around the Oracle optimizer.
These are my notes from the session, which include comparisons of how Oracle works (which Maria gave) and how MySQL works (which I researched to figure out the difference, which is why this blog post took a month after the conference to write). Note that I am not an expert on data warehousing in either Oracle or MySQL, so these are more concepts to think about than[Read more...]
If you’ve been reading up on the various NoSQL offerings and have wanted to try out one but don’t know how to get started, this is one of the easiest ways. I chose MongoDB for this example because I’m going to start using it for a project that needs features that MySQL isn’t as fast at: namely denormalized data with billions of rows. MongoDB has plenty of drivers for other scripting and high-level languages but I’ll focus on the PHP driver today. If there is interest I can do a write up on Python usage later. This example is limited to CentOS, Fedora, and Redhat 5 servers that use the yum package management system. For more information you can reference their download page: http://www.mongodb.org/display/DOCS/Downloads
First install the prerequisites:
During my work with companies using MySQL, I have encountered many issues with regard to schema design, normalization and indexing. Of the most common errors are incorrect data types definition. Many times the database is designed by programmers or otherwise non-expert DBAs. Some companies do not have the time and cannot spare the effort of redesigning and refactoring their databases, and eventually face poor performance issues.
Here’s a compilation of “the right and the wrong” data types.
INT(1)is not one byte long.
INT(10)is no bigger than
INT(2). The number in parenthesis is misleading, and only describes the text alignment of the number, when displayed in an interactive shell. All mentioned types are the same INT, have the same storage capacity, and the same range. If you want a
I’ve had one session accepted at the 2008 MySQL Conference & Expo (which is a good thing since I only submitted one session for nomination).
This year’s session is titled How to be Normal, a Guide for Developers. I decided to return to my old Normalization talk, but lean more towards the practical than the theoretical this year and work on common scenarios faced by developers rather than walk a person through the normal forms like I have previously done.
Here’s the abstract I’ll be sending in to replace the ugly one I submitted with:
At some point in every software project involving a database it becomes necessary for the developers who created (or inherited) the project to step back and take a look at their database. Many projects have a
|Showing entries 1 to 12|