Best method of organizing database?

Question

MORE DETAILS:

Both of you recomend using JOIN. But the main problem is how to assign multiple SUBJECTS PER EACH CLASS without using multiple duplicate values. I will have ~200 de classes, with ~30 subjects per class. That means if 2 classes share the same 20 subjects, i will have 40 rows, all with "class_id = 1" but with "subjects_Id =1, subjects_id=2, etc" Its not very ergonomic. Any other ideas? Thanks for your time!

So, I am here again asking for your time and help friends.

I have a database that its almost ok. But I am stuck at trying how to link multiple values from a table to on collumn on another.

Let me be more explicit.

I have this table:

    CLASSES
id | class_name | Matters   | 
-----------------------------
1  | Class1     | 13.4.2013 | 
2  | Class2     | 14.4.2013 |

And this table:

Subjects

mat_id | show title | 
-----------------
   1   | English    | 
   2   | French     |

Now the problem is this. Each CLASS (e.g. CLASS1) should be able to study more Subjects at once. For example, CLASS 1 should be linked with subject (mat_id) 1, 3, 5, 6.

How to do this without repeating myself, and optimize the database? I tought that I should do it like so, but its not convenient :

CREATE A NEW TABLE named

SUBJECTS_PER_CLASS

id | class_id   | mat_id    |
----------------------------
1  | 1          | 1         | 
2  | 1          | 3         |

BUT then I dont know how to query it. Any ideas? Any help will be greatly appreciated!

THANKS!

You are describing a many to many relationship in a normalized database. If you don't know what that means, I've heard good things about the book, Database Design for Mere Mortals. — Dan Bracuk, Sep 18 '13 at 18:19
that's exactly what you do. have an intermediate table to handle the linkages between your two main tables. this is called a many-to-many relationship. — Marc B, Sep 18 '13 at 18:20
yes, but i really need to have multiple identical "class_id" in the "subjects_per_class"? I mean is there no other way to assign more "mat_id"'s for each class_id? Thanks for your time! — Acelasi Eu, Sep 18 '13 at 18:45
Yes, the same class_id occurs multiple times in the subjects_per_class table, once for every subject that the class studies. That is the correct structure, which allows you to enforce uniqueness at the RDBMS level on class and subject. — David Aldridge, Sep 18 '13 at 18:52

score 3 · Accepted Answer · edited Sep 18 '13 at 18:32

3

SELECT 
 * 
FROM 
  CLASSES 
JOIN 
  SUBJECTS_PER_CLASS 
ON 
  CLASSES.ID = SUBJECTS_PER_CLASS.class_id
JOIN 
  Subjects 
ON 
  Subjects.id = SUBJECTS_PER_CLASS.mat_id

edited Sep 18 '13 at 18:32

Imran

3,031
4
25
41

answered Sep 18 '13 at 18:23

johnlemon

20,761
42
119
178

score -1 · Answer 2 · edited Sep 18 '13 at 19:00

-1

You can use join command.

Reference 1

Reference 2

edited Sep 18 '13 at 19:00

answered Sep 18 '13 at 18:20

Mojtaba GS3

51
1
12

Best method of organizing database?

2 Answers2