Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Data modelling is the art of representing the real world in a relationship diagram. Your mode is correct but is it true? </p> <p>Consider, what is a CLASS? It's a TEACHER, a SUBJECT and a GRADE. Those are your relationships. In addition you want to enforce the rules that the SUBJECT is appropriate for that GRADE and that the TEACHER can teach that SUBJECT in that GRADE. </p> <p>I think your problem lies in the use of surrogate keys in the intersection tables. These are the tables which represent your many-to-many relationships: <code>teacher_subject</code>, <code>grade_subject</code>. These are synthetic tables anyway, and they only consist of keys anyway. Hence a composite primary key will suffice. </p> <p>Surrogate primary keys have no meaning, so we need to define a unique constraint on <code>grade_subject(subject_id, grade_id)</code> to ensure we don't have two records for ('PHYSICS', 'YEAR 2'). Given that <code>grade_subject</code> is an intersection table with no other columns adding a surrogate key is pointless. The value of surrogate keys is to minimise the impact of a business key changing. But <code>grade_subject</code> doesn't have a business key, just two surrogate keys, <code>subject_id</code> and <code>grade_id</code>.</p> <p>This has an advantage when it comes to defining referential integrity.</p> <p>So I would approach your problem this way:</p> <pre><code>grade_subject ---------------- grade_id subject_id primary key (grade_id, subject_id) foreign key (grade_id) reference grade (grade_id) foreign key (subject_id) reference subject (subject_id) teacher_subject --------------------- teacher_id grade_id subject_id primary key (teacher_id,grade_id, subject_id) foreign key (grade_id) reference grade (grade_id) foreign key (subject_id) reference subject (subject_id) foreign key (teacher_id) reference teacher (teacher_id) foreign key (grade_id,subject_id) reference grade_subject (grade_id,subject_id) Class ----------------------------------- id teacher_id grade_id subject_id primary key (id) foreign key (grade_id) reference grade (grade_id) foreign key (subject_id) reference subject (subject_id) foreign key (teacher_id) reference teacher (teacher_id) foreign key (grade_id,subject_id) reference grade_subject (grade_id,subject_id) foreign key (teacher_id,grade_id,subject_id) reference teacher_subject (teacher_id,grade_id,subject_id) </code></pre> <p>This may look like a pile up of foreign keys, and I can envisage some arguments at the review stage. (I include the foreign key on <code>grade_subject</code> so I've got something to concede which I don't care about that much and so could concede). </p> <p>But I don't like having significant data relationships such as CLASS_SUBJECT obfuscated by being enforced through a subsidiary relationship such as TEACHER_SUBJECT. I don't want to have to join to TEACHER_SUBJECT and SUBJECT_GRADE in order to join CLASS to SUBJECT.</p> <p>Now, why do I choose to include referential integrity constraints to the single parent tables and the intersection table when the latter indirectly enforces the former? Because it makes the relationships clearer <em>in the model</em>. I stress in the model, because in the physical database I might choose to omit the single table foreign keys, or disable them, and trust in the relational integrity of the intersection table. </p> <hr> <p>Something about a triple column composite bugs me, and I think it's this: it's not sufficiently normalised. You may have a special case but the more general model would be two rules, TEACHER_SUBJECT and TEACHER_GRADE. That would look like this</p> <pre><code>teacher_subject --------------------- teacher_id subject_id primary key (teacher_id, subject_id) foreign key (subject_id) reference subject (subject_id) foreign keye (teacher_id) reference teacher (teacher_id) teacher_grade --------------------- teacher_id grade_id primary key (teacher_id,grade_id) foreign key (grade_id) reference grade (grade_id) foreign key (teacher_id) reference teacher (teacher_id) Class ----------------------------------- id teacher_id grade_id subject_id primary key (id) foreign key (grade_id) reference grade (grade_id) foreign key (subject_id) reference subject (subject_id) foreign key (teacher_id) reference teacher (teacher_id) foreign key (grade_id,subject_id) reference grade_subject (grade_id,subject_id) foreign key (teacher_id,subject_id) reference teacher_subject (teacher_id,subject_id) foreign key (teacher_id,grade_id) reference teacher_grade (teacher_id,grade_id) </code></pre> <p>Of course, if you still wanted to enforce a rule that Mr Drury could only teach Maths to fourth formers and Physics to sixth formers you would need a TEACHER_SUBJECT_GRADE table.</p> <hr> <p>One thing your data model doesn't address is the problem of scheduling. School timetables have to mesh, so Classes need to fit into a pre-determined grid. You probably need to define the timetable's slots as a separate table and link CLASS to that. Classes also need Classrooms. That's another table.</p>
    singulars
    1. This table or related slice is empty.
    1. PODesign suggestions for Teachers,subjects and grades database
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAPC
    1. USAPC
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PODesign suggestions for Teachers,subjects and grades database
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThanks APC for your answer and time . There are two thing in your design that my poor brain cannot understand. 1)surrogate keys: wouldn't a unique (auto increment) integer be sufficient ? what are the advantages of using composite primary key? (2)In teacher_subject table, why are you using (grade_id,subject_id) as foreign key whereas grade_id and subject_id are already been used as foreign keys individually? Sorry for my ignorance. But if you can explain this to me , I would be grateful to you. Have a nice weekend.
      singulars
      1. PO
      1. USBishnu Paudel
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload