What is the best practice to use FK as PK, or use surrogate PK, and FK just as FK in JPA context? I saw few times people said that they had to map FK as PK, because they had legacy database. So does it mean that for new tables, if you have control to create them it is better to use the following structure:
TABLE_1
-------
ID (PK)
...
TABLE_2
-------
ID (PK)
TABLE_1_ID (FK)
instead of:
TABLE_2
-------
TABLE_1_ID (PK) and (FK)
For many to one relationships, always use the first alternative you presented.
For some one-to-one relationships, the tables may be merged with no ill effects.
Where your second alternative really becomes useful is when implementing superclass-subclass hierarchies using the class table inheritance model, as presented by Martin Fowler. In this case, you want to keep the subclass tables distinct from the superclass table, in order to reduce the number of NULLS. But the relationships are one-to-one.
By making the same key function as both a PK and an FK in the subclass tables, and by making the FK reference the matching entry in the superclass table, it makes it super easy to join the specialized data with the generalized data, when desired. This can be called "poor man's inheritance".
In this particular case (1 to 0..1 relationship), consider merging two tables into one.
If they are split intentionally (e.g. for "vertical" partitioning), prefer the same field being both PK and FK.
Only consider adding another key if you can make it smaller1, but balance this with the need for additional index2, potential hostility towards clustering3 and a need to model diamond-shaped dependencies4.
1 E.g. because TABLE_2.TABLE_1_ID is string and you can make the TABLE_2.ID integer.
2 Every new index slows-down INSERT and can slow-down UPDATE and DELETE depending on their WHERE clause. Also, any additional data puts additional pressure on cache making it "smaller".
3 Secondary index in a clustered table needs to contain a copy of the PK and can cause a double-lookup (first for index and then for PK) when locating rows.
4 Using identifying relationships on both "edges" of a "diamond" may be necessary to ensure that the "bottom" of the diamond references a single "top".
