Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 12 2 Older Entries

Displaying posts with tag: Normalization (reset)

How to structure and design a relational database to support you data storage needs?
+0 Vote Up -0Vote Down
Well, every now and then, when we began to start a new project or app, which has some data storage requirement, we have a deep intriguing thought as to how best represent the data structure so as to support a variety of needs including but not limited to (ACID rules):

1. Normalization
2. Reliability
3. Consistency
4. And many others

Below, I provide a set of steps which you can follow to arrive at a data model that correctly suites your requirements.


1. Identify the project or app requirements / specifications and business rules which tell you what your app will be able to do when it is ready.
2. From these business rules, identify possible objects for each business rule and mark them in a paper using rectangular sections like authors,

  [Read more...]
Normalize Your MySQL Database For Efficient Data Access, Query Flexibility and Maintenance
Employee_Team +0 Vote Up -0Vote Down

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...]
Every Relation is in First Normal Form, by definition. Every Table may not.
+2 Vote Up -0Vote Down
Note: I use a dual terminology(or even triple considering Database Design theory) when I am talking about Databases and their elements.   In Relational Model terminology
  • Relation
  • Tuple
  • Attribute
  • In SQL terminology

  • Table            
  • Row
  • Column

    read more

    Dynamic de-normalization of attributes stored in key-value pair tables
    +0 Vote Up -0Vote Down

    Dear Kettlers,

    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...]
    Databases: Normalization or Denormalization. Which is the better technique?
    +1 Vote Up -0Vote Down
    This has really been a long debate as to which approach is more performance orientated, normalized databases or denormalized databases. So this article is a step on my part to figure out the right strategy, because neither one of these approaches can be rejected outright. I will start of by discussing the pros and cons of both the approaches. Pros and Cons of a Normalized database design. Normalized databases fair very well under conditions where the applications are write-intensive and the write-load is more than the read-load. This is because of the following reasons: Normalized tables are usually smaller and...
    Data Warehousing Best Practices: Comparing Oracle to MySQL pt 1
    +4 Vote Up -3Vote Down

    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...]
    Performance tuning using vertical partitioning.
    +0 Vote Up -0Vote Down
    Does having small data-sets really help? Of course it does! Are memory lookups faster that disk lookups. Of course ! So many times I have seen people complain about queries taking too long now, while they were not taking that long earlier. There is one big reason for this, earlier the size of data-set was small so it could fit into memory. Now that the data-set has grown large enough that it cannot fit entirely into memory, the disk seeks really have slowed down the queries significantly. What to do now? Vertical partitioning. Divide the data-set into separate data-sets vertically....
    How to install MongoDB on CentOS 5.4 / RHEL5 and interface with PHP 5
    +0 Vote Up -1Vote Down

    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:

    First install the prerequisites:

    • sudo yum
      [Read more...]
    Normalization and smoking
    +0 Vote Up -0Vote Down

    An interesting question came yesterday at the end of the MySQL workshop in Athens. An user has a server with about 40,000 tables, and the performance is not brilliant (oh really?). The reason is that there is an application that requires one or more new tables for any user, and the tables are of about ten different structures.
    The user asked the wrong question: "will the performance improve if I change the storage engine from InnoDB to MyISAM?"

    Salle gave the first answer. Converting the tables to MyISAM would only exacerbate the problem. Instead of

      [Read more...]
    Common wrong Data Types compilation
    +0 Vote Up -0Vote Down

    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
      [Read more...]
    Showing entries 1 to 10 of 12 2 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.