This has been asked before on SO.
Where you have common and very ambiguous names, then prefix with table name. That is, anything you're liable to have to alias in almost every query.
So for an Employee table I'd have
EmployeeID
EmployeeName
Comment
Salary
StartDate
EndDate
InsertedDateTime
...
And Wikipedia actually says:
The USING construct is more than mere syntactic sugar, however, since the result set differs from the result set of the version with the explicit predicate. Specifically, any columns mentioned in the USING list will appear only once, with an unqualified name, rather than once for each table in the join.
That is one less column. You'd never use SELECT *
anyway so the point is moot...
Here is a suggestion so you can enforce the constraints you want declaratively. (I've simplified the table names a bit, removed the bridge_
prefix.)
We remove footnote_num
from:
Table: a_ref -- was named: bridge_a_reference
a_id,
ref_id
Primary key:
(a_id, ref_id)
Foreign keys:
a_id -> a
ref_id -> reference
We add this table - which will basically store only those rows from a_ref
with footnote, those you want to add children into the b_ref
:
Table: a_ref_with_footnote
a_id,
ref_id,
footnote_num
Primary key:
(a_id, ref_id)
Unique key:
(a_id, footnote_num)
Foreign keys:
(a_id, ref_id) -> a_ref
And finally the 3rd table stays as in your design except the foreign keys which now reference the intermediate table (a_ref_with_footnote
):
Table: b_ref -- was named: bridge_b_reference:
a_id,
b_id,
ref_id,
Primary key:
(b_id, ref_id)
Foreign keys:
(a_id, b_id) -> b
(a_id, ref_id) -> a_ref_with_footnote
Best Answer
I am sure there are plenty of them. Choose one.
For example, I would name the reference
CodeREF
And if having multiple I would use describtive names as per the objects that I will manifest (RawCoderREF, ReviewedCodeREF)