Menu
what do they mean when a database is “flattened out” vs. normalized?
753
However, maintaining redundant sets of normalized and denormalized data has its own administrative costs. Denormalized, or flattened, tables, can minimize these problems. Flattened tables can include columns that get their values by querying other tables.
Data flattening usually refers to the act of flattening semi-structured data, such as name-value pairs in JSON, into separate columns where the name becomes the column name that holds the values in the rows. Data unflattening is the opposite; adding nested structure to relational data.
Normalization increases the number of tables and joins. In contrast, denormalization reduces the number of tables and join. Disk space is wasted in denormalization because same data is stored in different places. On the contrary, disk space is optimized in a normalized table.
Denormalization is the process of adding precomputed redundant data to an otherwise normalized relational database to improve read performance of the database. Normalizing a database involves removing redundancy so only a single copy exists of each piece of information.
"Flattened out" typically refers to a database where you have a single (or few) very large tables.
"Normalized" refers to whether the data has been organized into well structured, related tables. This typically reduces duplication of values across rows in a table by pulling the values into a separate table, and relating to it by ID.
For details, see Database Normalization.
68
A normalized database is one that is organized to minimize redundancy of data and to produce small and well structured relationships, normally via related tables. An example might be a customer and all his/her orders. In a normalized database, you would have at least two (and probably more) tables. A customer table and an orders table, joined together in some fashion. In a flattened structure, customer and order data might be in a single table.
Reporting databases tend to be denormalized to allows quicker retrieval of data (where many joins may be required), whereas production or transactional databases (OLTP) tend to be (or should be) more normalized with foreign keys established between tables.
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
