Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Data Modeling: Is it always necessary to use an intersection table?

Consider the following:

I have two tables (Customers and Cases) defined as follows:

**Customers**
CustomerId (PK)
CustomerName
CustomerPhone

**Cases**
CaseId (PK)
CaseManager
CaseNotes

Each customer can have an unlimited amount of cases, however each case can only belong to one customer. So in order to relate the tables to each other, I would use an intersection table (CustomerCases), that consists of the foreign keys from each table respectively.

However, couldn't I just add the CustomerID from the Customers table as a foreign key to the Cases table? I feel like I'm missing something. Are there any pitfalls that I should be aware of should I choose not to use an intersection table?

like image 665
TelJanini Avatar asked Aug 28 '11 04:08

TelJanini


2 Answers

If one case can belong only to one customer, it seems totally reasonable to me that you just add a CustomerID FK to the Cases table.
If you think that requirement might eventually change (e.g. a case might have multiple customers), then the intersection table approach might make more sense.
Also unless you have an indefinite number of CaseManagers, it might also make sense to have a Managers table and have a FK from the Cases table to that.

like image 129
Aditya Mukherji Avatar answered Nov 08 '22 11:11

Aditya Mukherji


An "intersection" (aka "junction" or "cross-reference") table is only necessary to model a many-to-many relationship. In this situation, a Case participates in a many-to-one relationship with a Customer. So, a foreign key is all that is required -- and indeed that would be a more conventional solution. Avoid the unnecessary complexity of a junction table -- unless there is an architectural reason to the contrary (e.g. the application relies upon a component that demands that all relationships be represented using junctions).

like image 30
WReach Avatar answered Nov 08 '22 12:11

WReach