在SQLite中如何组织这样的数据库？ [英] How do I organize such database in SQLite?

问题描述

人们！我需要一些帮助组织数据库的应用程序，我不知道如何做到这一点。假设如下：
有一个学科的列表。对于每个科目，我们需要有一个参加这个课程的学术团体名单。然后，对于每个组，我们需要一个日期列表。对于每个日期，我们需要有一个学生名单，以及这个学生是否在那天出席。
我的头脑中有丑陋的数据结构，会感谢任何帮助。

更新

如何查看：

Table1（第一栏是日期和第二列表学生的身份证，谁在场）

  10/10/11 | id1，id2，id3 
 
 10/11/11 | id1，1d3，id5 
  

Table2：

  subject1 | id1 id2 id3 
 
 subject2 | id3 id2 
  

再次，ids是组的ID。不知道如何连接这些表。

解决方案

设计数据库时需要考虑许多因素，但是根据您目前提供的信息，一个很好的开始：

这个ER模型使用很多识别关系（即将父母的主键迁移到孩子的PK），并导致自然主键，而不是需要使用代理键的非识别关系。现在很多人都喜欢替代键，但事实是两个设计策略都有利弊。特别是：

• 自然键较大（它们通过多个级别的父子关系积累）。


• 而且，自然键需要较少的加入。


• 在某些特殊情况下（如菱形依赖），自然键可以更好地执行约束。

folks! I need some help with organizing database for application and I have no idea how to do it. Suppose following: There is a list of academic subjects. For each subject we need to have a list of academic groups, which attend this subject. Then, for each group we need to have a list of dates. And for each date we need to have a list of students, and whether this student was present that day or not. I have ugly data structures in my mind, will appreciate any help.

UPDATE

How do I see it:

Table1(the first col is date and second is list student's id, who were present)

10/10/11 | id1, id2, id3

10/11/11 | id1, 1d3, id5

Table2:

subject1 | id1 id2 id3

subject2 | id3 id2

And again, ids are id of groups. Dont know how to connect those tables.

解决方案

There are many considerations to balance when designing a database, but based on the information you provided so far, something like this might be a good start:

This ER model uses a lot identifying relationships (i.e. "migrating" parent's primary key into child's PK) and results in natural primary keys, as opposed to non-identifying relationships that would require usage of surrogate keys. A lot of people like surrogate keys these days, but the truth is that both design strategies have pros and cons. In particular:

• Natural keys are bigger (they "accumulate" fields over multiple levels of parent-child relationships).
• But also, natural keys require less JOINing.
• Natural keys can enforce constraints better in some special cases (such as diamond-shaped dependencies).

这篇关于在SQLite中如何组织这样的数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！