数据建模 – 嵌套对象 vs 单独的数据库表

huangapple go评论79阅读模式
英文:

Data modelling - Nested object vs Separate DB Table

问题

我设计了一个便签管理应用程序(CRUD),其中便签具有3个属性,标题、摘要和任务列表。任务具有2个属性 - 标题和摘要。

一个便签可以有最多1000个任务。

类会像这样 -

public class StickyNote {

    String id;

    String heading;

    String summary;

    Set<Task> tasks;

}

当涉及到使用 Database 时,对我来说,NoSql/文档结构似乎更接近面向对象编程。因此,我会有一个表代表 StickyNote,将任务作为一个字段/列。

这种方法的优点 -

  1. 删除 - 由于多个便签可以有相同的任务,因此通过提供任务标题和便签id,删除任务会比较简单。

  2. 无需引入第二个任务表。因此在删除时要么避免重复记录,要么避免外键约束检查。

  3. 更新 - 任务将使用任务标题或任务摘要之一进行更新。因此迭代任务列表,由于最多可以有1000个任务,性能不会成问题。

缺点 -

  1. 使用id来删除任务更为直接和高效,而不是使用迭代器。

问题-

  1. 引入任务表会提高性能效率吗?
  2. 相对论方法是否在某种程度上优于非关系方法?
  3. 如果我考虑扩展应用程序,在并发环境中使用百万用户,引入任务id将是强制性的,因为迭代会降低性能,这实质上会导致添加一个单独的任务表。因此,在这种情况下,NoSQL 和 SQL 方法将是相同的吗?
英文:

I'm designing Sticky notes Management Application (CRUD), where StickyNote has 3 attributes, heading, summary and task list. The task has 2 attributes - heading and summary.

A sticky note can have upto 1000 tasks.

The class would look like -

public class StickyNote {

    String id;

    String heading;

    String summary;

    Set&lt;Task&gt; tasks;

}

When it comes using Database , to me a NoSql/Document structure seems more close to OOPS.Thus having one table representing StickyNote, with Task as a field/column.

Pros of this approach -

  1. Deletion - As multiple sticky notes can have same tasks, thus deletion of task could
    be straightforward, by providing task heading and sticky notes id.

  2. No need of introducing a second table for tasks. Hence either avoiding duplicate
    records or foreign key constraint check while deleting.

  3. Update - a task would be updated either using task heading or task summary. Thus
    iterating over the task list, and as max tasks could be 1000, performance won't be an
    issue.

Cons -

  1. Using id for deleting task is straightforward and performance efficient, rather than using iterator.

Questions-

  1. Would introducing Task table would be performance efficient?
  2. Would relational approach be some how better than non relational one?
  3. If I consider to scale up application, using million users in concurrent environment, introducing a Task id would be mandatory as iteration would then degrade performance, which essentially would lead to add a separate Task Table. Hence in that case both nosql and sql approach would be same?

答案1

得分: 0

我找到的答案,经过尝试了关系型和非关系型方法之后 -

在这里,最好的解决方案是在任务中引入新的表格,而不是使用嵌套结构。
这样可以在任务上执行CRUD操作,而无需遍历集合(使用@Id属性),从而使其性能高效。
然而,如果只需要对任务进行偶尔的只读操作,文档结构是最合适的。

因此,在任何情况下,NoSQL和SQL方法都不会相同。NoSQL最适合非结构化数据,其中非结构化数据很少被更新。一旦非结构化数据需要频繁更新,最好进行结构化以提高效率。

关于重复记录 - 在StickyNotes中使用Tasks的@Id属性作为参考,并在删除时移除引用的项目。

英文:

Answers I found, after trying both relational and non relational approaches -

The best solution here is to introduce new table in Tasks rather than using embedded structure.
This facilitates in performing CRUD operations on the task without iterating over collection (using @Id attribute), hence making it performance efficient.
However if only seldom read only operations are needed on tasks, document structure is best suited.

Hence in no case, Nosql and Sql approaches are would be same. Nosql is best suited for unstructured data, where the unstructured data is seldomly updated. As soon as the unstructured data needs frequent updates, it is better to structure for efficiency.

Regarding the duplicate records- use @Id attribute of Tasks as reference in StickyNotes and remove the referenced item when deleted.

huangapple
  • 本文由 发表于 2023年6月29日 17:56:10
  • 转载请务必保留本文链接:https://go.coder-hub.com/76579977.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定