When to use an associative table instead of 1 foreign key?

Question

There are plenty of examples of both associative table and one foreign key designs out there, but I can't find clear any explanation of when should each of them be preferred.

For example let's assume my database needs to store the following information about people: first name, last name, address. I have 2 options of implementing this:

Option 1: associative table

Table: person
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+

Table: address
+----+---------+-------+------+-----+-----+
| id | country | state | city | zip | etc |
+----+---------+-------+------+-----+-----+

Table: join_person_address
+-----------+------------+
| person_id | address_id |
+-----------+------------+
(both columns are foreign keys)

Option 2: one foreign key (in this case in the address table)

Table: person
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+

Table: address
+----+---------+-------+------+-----+-----+-----------+
| id | country | state | city | zip | etc | person_id |
+----+---------+-------+------+-----+-----+-----------+
(person_id is a foreign key)

What I learned so far

Associative tables are always used for many-to-many relationships.

One foreign key design may create situations that don't make sense for the data model (as in the example above - making an address reference a person when the address is the person's property and not the other way around, and making the person reference the address limits it to referencing only one address).

One foreign key design can be sufficient for one-to-one and one-to-many relationships but sometimes it may become required to change some relationships unexpectedly, so using a single foreign key design can be risky.

Conclusion: always use associative tables unless you can guarantee that the type of relationship won't ever change AND using one foreign key makes sense for the data model. Or to stay on the safe side: always use associative tables unless there is a really good reason not to.

The question

Based on what should I decide which option to use? (not specifically for the person-address relationship, that's just an example)
And why? (what are the advantages and disadvantages)

Is what I learned so far correct or did I get this wrong?

Answer 1

The choice between the 2 cases you listed is not optional. Each one serves a different purpose. The use of FKs comes from mapping requirements to the data model. It is not a pure design decision.

Your first example, is correct when you know that the same address information will be used by more than one person in your database. For example, if you are building a school database, there is a good chance that 2 students live in the same house. In such a case, you have to use a m-m relationship.

Your second case, is correct when you know that a person could have more than address (maybe over the application time), but no two addresses are exactly the same. In some cases this could cause duplication since you can't guarantee that address 2 is different from address 1, the "ect." stuff may contain text that can be re-worded but still gives correct real meaning.

This second case, is also similar to the famous "invoice - detail line" case. There is no value in modeling such a case with an Associative Table.

About the conclusion you have listed about using associative tables: An Associative Table is an approach to overcome some RDBMS inability to implicitly represent Many-to-Many relationships.

When to use this approach depends on the nature of the data and the business requirements. It is "wrong" to use an Associative table when you don't have to, because it makes the database open for logical errors, and introduce unnecessary complexity.

It is also "wrong" to use 1-M when you don't care about more than one occurrence. In your case, if you only care about 1 address, you would still have a 1-M but with a maximum of 1 occurrence at the many side (not trivial to enforce), or you may have the Person table and Address table all in one table in the database (makes sense sometimes, purists hate it).

Answer 2

It really depends on the actual use case whether association tables or simple foreign keys are the right way to correlate data, you already pointed out the main differences and you're certainly able to choose the fitting solution in most cases easily, and in tricky cases after some discussion with coworkers and some experiments.

But I would strongly advise against using association tables everywhere just because you may need them in the future. Keep the schema as simple as possible, and don't over-engineer if there is no clearly defined need. Experience shows that you will very rarely recover the additional cost of implementing and maintaining an unnecessarily complex solution. A better approach is to adopt practices that make later change less painful, such as reducing dependencies on the schema to few modules, and versioning APIs so older clients don't always need to be changed when the database representation evolves.