Database Design Terminology – What Are Folded and Pivoted Tables?

database-designterminology

The answers and discussion on questions like this one talk about folded vs pivoted tables.

eg.

The only difference between a paper presentation and a sound
electronic presentation is that a folded table structure is more
convenient in the latter case while a pivoted table structure in the former…

I couldn't find anything on Google about what those terms mean (Google gave results about protein structures).

Could someone please explain what those terms mean with examples?

Best Answer

It's just a different way of representing the data. Also known as wide (pivoted) and long (folded). As an example and to keep it simple, imagine that we have a record with 3 properties: id, name, description in the wide (pivoted) version each property would be its own column like so:

CREATE TABLE `example_table` (
  id int not null,
  name archer(50) not null,
  description archer(100) null
)

In the long (folded) format you would have two columns like so:

CREATE TABLE `example_table` (
  property varchar(50) not null,
  value varchar(100) not null
)

The later gives you greater flexibility if the columns are not known in advance or are likely to change often. However, it is generally more complicated to implement because you have to design a way of knowing what type the value is, and then you have the overhead of serialization/deserialization to the storage type (in this example varchar(100)).

The wide format is better when the properties do not change very often and are known in advance. This will offer better performance but will be less flexible if changes are required.

Generally, where possible choose the wide format when you know the structure of the data you are storing, and choose long if you need maximum flexibility and are willing to accept the added complexity.

Related Question