Search code examples
mysqlsqlentity-relationshipforeign-key-relationshipcomposite-primary-key

Foreign Key or Composite Key?

I just started applying everything that I read about table relationships but I'm kind of confused on how to insert data on tables with MANY-TO-MANY relationship considering there's a third table.

Right now I have these tables.

subject

name
code PK
units
description

schoolyear

schoolyearId PK
yearStart
yearEnd

schoolyearsubjects (MANY TO MANY table)

id PK
code FK
schoolyearId FK

But the problem with the above schoolyearsubjects table is that, I don't know how I can insert the schoolyearId from the GUI. On the GUI screenshot, as soon as "Save" button is clicked, a TRANSACTION containing 2 INSERT statements (to insert on subject) and (to insert on schoolyearsubjects) will execute. If I stick with the above, I'll have to insert the schoolyearId. schoolyearId definitely won't come from GUI.

enter image description here

I'm thinking of changing the columns of schoolyearsubjects and schoolyear to this:

schoolyear

--composite keys (yearStart, yearEnd)
yearStart (PK)
yearEnd (PK)

schoolyearsubjects(MANY TO MANY table)

id PK
code (FK)
yearStart (FK) --is this possible?
yearEnd (FK) --is this possible?

1.) Is the solution to change the columns and make a composite key so I can just insert yearStart and yearEnd values instead of schoolyearId?

2.) Is my junction / linking table schoolyearsubjects correct?

3.) What can you advise?

I'd appreciate any help.

Thanks.

Solution

  • For me schoolyear is a period, and as such, there is no need to use a surrogate key here. This always makes things more confusing, and it is always more difficult to develop a graphical interface for it (I'm talking about how we model periods as developers).

    If you stop to think, periods are seen itself as something unique. Will you have a period equal to the other? Stop and think. Even if you have, this will occur in years or different times. So we already have a primary key for schoolyear. Eliminate "schoolyard PK" from schoolyear. Use composite key here with yearStart and yearend. So, your schoolyear entity (in future, table) will be like:

    • yearStart PK
    • yearEnd PK

    In the intermediate table, you will have 3 fields as composite primary key (also foreign key!):

    • yearStart PK FK (from schoolyear)
    • yearEnd PK FK (from schoolyear)
    • code PK FK (from subject)

    This will permit that a period have only a single subject. If, on the other hand, you want a period with more than one subject, you would have to put a surrogate key here.

    Now, to draw the graphical interface, you will only have to use a select box (combo box). In this case you will have each item as a text, something like "from year X to Y" (a period). Your users can very well understand and select it.

    Note: In anyway, you may not have the ID of a record in an interface, but the values that identify it. This is permissible to be seen, and identifies a record of their remaining.

    If, however, you do not have periods as something unique, then "yearStart" and "yearEnd" are fields in subject entity, and there is no schoolyear entity. To be honest, the entity "schoolyear" should only exist if you want to reuse it's records to relationships with other records of other(s) table(s). I'm not saying this is or is not the case. Watch out as well. If you do this you say that every period has only one subject (as fields). I do not know if this is exactly what you want. We must always remember the most important thing in shaping an ER-Diagram:

    • CONTEXT

    Check your context. What does it ask? If you have any questions, please comment. If you can offer me some more context here, I can help you more.