Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>+1 for Matt Fenwick. I would add that you want to be a little careful with your foreign key constraints. You essentially have two options, both of which could end up looking pretty similar, depending on your choice of primary keys.</p> <p><strong>Option one</strong> is: Forget the simple intersection between <code>TEACHER</code> and <code>INSTRUMENT</code> and replace it with a complex intersection that includes <code>teacher_id</code>, <code>instrument_id</code> and <code>level_id</code>. All three of these columns would be the (compound) primary key of this intersection table. In this option, you have foreign key constraints defined on <code>teacher_id</code> and <code>instrument_id</code> (and <code>level_id</code> if this is actually a foreign key to a <code>LEVEL</code> table and not just an integer or string code).</p> <p><strong>Option two</strong> is: Keep the simple intersection between <code>TEACHER</code> and <code>INSTRUMENT</code> (let's call it <code>TEACHER_INSTRUMENT</code> even though that is unimaginative) and add a sub-child table that defines the levels that can be taught. This sub-child table (let's call it <code>SKILL</code>) has a <code>level_id</code> and a foreign key to <code>TEACHER_INSTRUMENT</code>. If the primary key of <code>TEACHER_INSTRUMENT</code> is the combination of <code>teacher_id</code> and <code>instrument_id</code> then the <code>SKILL</code> table is going to have the same three columns as in option one. What makes this option different? <em>The foreign key constraint from</em> <code>SKILL</code> <em>must be to the intersection table, not to</em> <code>TEACHER</code> <em>and</em> <code>INSTRUMENT</code>.</p> <p><strong>Why is this important?</strong> If you choose option one, you may need to have some extra query logic to get a fully populated grid of skills, since there is no referential integrity constraint you can define that will ensure that all skill levels are populated for each teacher/instrument combination.</p> <p>On the other hand, if you choose option two, you have the advantage of separation of concerns between who can use what and how well they can teach it. </p> <p>What you want to avoid is having one table that contains just the teacher/instrument relationship and then a second one which (independently) repeats that relationship but adds the skill level detail. If you do that, then you run the risk of these two things getting out of sync.</p>
    singulars
    1. This table or related slice is empty.
    1. POLinking a third table to a bridge table in a many to many association
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USJoel Brown
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COPerfect thanks for the detailed answer! Both answers were very helpful.
      singulars
      1. PO
      1. USBrian Jenkins
 

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