How do I organize such database in SQLite?

folks! I need some help with organizing database for application and I have no idea 开发者_JAVA百科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).

---

You will design one table for each kind of "thing" (subjects, groups, students, meetings) in your database. Each table will have one column for each datum (piece of information) you need to store about the thing. Additionally, there must be one column, or a predictable combination of columns, that will allow you to uniquely identify each thing (row) that you store in the table.

Then, you will decide how the things (subjects, groups, students, meetings) are related to each other and make sure that you have the correct columns in each table to store those relationships. You will discover that in some cases this can be done by adding one or more columns to the tables you already defined. In other cases, you will need to add a completely new table that doesn't store a "thing", per se, but rather a relationship between two things.

Once you have your list of tables and columns, if you feel that fails to represent some part of the problem correctly, post another question with the work you've already done and I'm sure you'll find someone to help you complete the assignment.

Response to your update:

You are on the wrong track. It is a bad idea (and contrary to correct relational database design) to ever store two values in a single field. So each of the tables you wrote about should have two columns (as you said), but the second column should store one and only one id. Instead of one row in table1 for 10/10/11, you would have three separate rows in your table.

But, before you start worrying about the "relationships", create tables to hold the "things".

I also suggest you pick up a basic guide to relational databases.