Trade-offs in database

    Last week, The group ADAM made a wonderful lecture for us. I appreciated their lecture a lot not only because of their creative video, their clear explanation but also the use of comparisons which inspires me to write this blog.

    When we make a comparison between similar concepts or approaches, the characteristics of each would be more evident and easier to understand. What’s more, we will find the trade-offs between alternatives which can provides us with advice when facing selections. So I will review the several comparisons in the half-term database learning and conclude the main trade-offs worth considering in database design.

Comparison I. Relational Model vs. E-R Model

Trade-off: Natural to users vs. Natural to designer

Related lecture: Relational Model & E-R Model

1. Relational Model is a logical structure while E-R Model is a conceptual structure.
2. Relational Model is more abstract and mathematical , while E-R model concerns more on semantics.
3. In a relational model, the relationship is constituted by Foreign Key, but in E-R model, the relationship is set up by relationship set.

Comparison II. Normalization vs. Denormalization

Trade-off: space vs. efficiency(speed)

Related lecture: Database design

When we design a database, we do normalization to reduce redundancy( 1NF->2NF->3NF), but when we reduce redundancy, it will be more complicated for use to get the data we want by visiting more linked tables. So we choose to do denormalization to improve the efficiency.

Comparison III. XML vs. Json

Trade-off: natural language vs. space

Related lecture: Data serialization

1. JSON is more concise and light-weight while XML is more recognized, widely used and well supported.
2. When mixed and long text is involved, XML is better than JSON for a more readable language is used

Comparison V. Ordered indices vs. B+-tree index

Trade-off: space vs. efficiency(speed)

Related lecture: Data Storage and querying(Our lecture in week11)

1. when use B+-tree index, we have a function to divide all the index into several buckets. So when we search one record, only one bucket would be searched instead of all the records. So efficiency is improved. However, some of the bucket might not be full so some space will be of redundancy.
2. But whether we should choose ordered indices or B+-tree index is also related to what kind of selection of data would be used frequently. When ordered data is searched more, ordered indices is better while B+-tree

So as we can see, (1)space and efficiency (2)natural and concise are trade-offs we often meet dealing with database design.