Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

normalization and denormalization?

How to identify the tables or in normalized state or denormalized state?

4 Replies
villegasi03
Creator
Creator

Ummm. Wow, that's going to be tough to answer in a few lines. In short:

  • You want to avoid duplicating data. For example if you have a table that contains a table of a Universities teachers, students and test scores. If you have a table that repeats the teacher name over each student for every class that the teacher has then you have redundant data.
  • You would then create a table specifically for all teachers which contains a teacher ID.
  • You can then relate that new table (now a reference table) with your details table.
  • Think, "Is this a high level master table?" or is this a "table with details related to a master table?"
Not applicable
Author

Hi Manoj,

Check the table viewer it will give you the idea that your  data model is normalized or denormalized.

Denormalized data model will resembles Star schema, Normalized data model will resembles Star flake,Galaxy kind of schema.

timothyjang
Partner - Contributor
Partner - Contributor

You could do this conceptually. A technical way would be pretty simple, just compare the distinct instances of an attribute vs. the row count of the table. If the distinct instances are smaller, you can replace that attribute with a key in the current table and create another table with the same key and the attribute. This would make sense for attributes that could potentially belong to another 'entity'.

Conceptually, when looking at an individual table, especially one that's normalized, it should have information only describing that entity. For example, a Stores table should only have fields that describe that store, having fields such as Store Name. Let's say that LocationID is the primary key for the Location table and has only one other field, Country. Also assume that this was the result of joining a Location table to the Store table and then dropping LocationID. The resulting table would contain Store and Country data and can be considered a 'denormalized' table. Now let's take this backwards. Let's say you realize that there's only three distinct values for Country in the Stores table, but you have 10,000 rows in the Stores table, so that the three distinct values for Country are repeated many times. To normalize this relationship you would replace the Country in the Store table with a key and create a Country table with the key (distinct) and attribute. This would lower the memory overall since instead of having something like 'Argentina' repeat thousands of times there be a numeric key in its place on the Stores table and a single instance of 'Argentina' on the Location table.

marcus_sommer

Qlik isn't a sql-database and therefore you won't get benefits from a normalized datamodel rather the opposite will work best. The official recommendation is to develop a datamodel in the direction of a star-scheme as the best compromize regarding to run-times, complexity and overall performance.

- Marcus