I have 2 tables, company and interns. the initial relationships are,
- An intern belongs to only one company.
- A company has many interns
I have a slight problem with these relationships.
If the student changes to another company halfway through but needs the record of the previous company to be kept, is it correct if I change the relationship to become 'An intern belongs to many company' and create a third table to handle the 'many-to-many' relationship?
Or should I just leave it at that and instead create a 'log' table that logs the students' previous company?
Or is there a better way?
Note : Imagine that this system requires the most efficient way of storing data and that student cannot belong to two or more different companies at the same time.