Hello.
What's the recommended way for naming linked field?
For example, if we have an employee table, being the "EmployeeId"
field the primary key, shouldn't all other tables that reference an
employee have a "EmployeeId" field as a foreign key?

I really think so. Can't see any advantage in not doing so, and many
disadvantages in not sticking to this convention. But I've seen so
many people disagreeing that I want to hear from you all.

Thanks.


Filipe Martins
http://www.i-senso.com

<filipe.martins.10 (AT) gmail (DOT) com> wrote

I'm surprised that you see many people disagreeing with this convention.
It's pretty standard to use data dictionaries in the software industry so
that the data elements are well defined. Note that there can be some
exceptions. If your employee table includes both the employee and his
manager which column is the EmployeeID? The answer is obvious but whatever
name you make for the manager column it represents another EmployeeID value
(except when the employee is the company president).

filipe.martins.10 (AT) gmail (DOT) com wrote:
There are IMO two aspects about naming foreign key attributes.
You want to make it clear what you are referring to, so using the same
name as the attribute you refer to is good for that.
However you also want it to be clear what the foreign key attribute is
used for. In many cases this should not be a problem, but there are
cases where the role of foreign key attribute is ambiguous. This is
especially true if one table contains several foreign keys referring to
the same primary key.

Axel Hallez

<filipe.martins.10 (AT) gmail (DOT) com> wrote

I see your question as containing two issues: the first is concerning
artificial keys like EmployeeId. The second is with regard to always using
the same key to reference a given table.

With regard to the first issue: EmployeeId, a number that uniquely
identifies an employee, is an artificial key. That's beyond doubt. Beyond
that, the question might be, who assigns an EmployeeId to a new employee?
In many cases, it's HR. During the employee's first day on the job, they
take a photo, transfer the photo to the new employee's badge, and also
print the Employee Number (or employeeid if you prefer) on that badge,
marking the connection between that Id and that employee.

Numbers are both more efficient and more effective for the purpose of
uniquely identifying employees than any natural key such as name might be.
They are also preferable, in some ways, to some other ID like Social
Security Number(SSN) in the US. This is extensively written up in the
fundamentals of databases, which I refer you to.

Another question might be whether it's HR or IT that ultimately assigns the
"next available number" to the "next incoming employee". That's a whole big
can of worms that I don't want to touch right now.

Next, in the case of an employee table, it's possible that there is more
than one candidate key. For example, EmployeeId and SSN. The question
might be whether it's better to always link a table reference via the same
key or whether it's better to use employeeId for some references and SSN for
others. In general, data management will be simpler and easier if all
references use the same key, the one designated "primary key". It should
be noted that which of several candidate keys is called "primary" is an
arbitrary choice.

There are cases where SSN might profitably be used. For example, if we
have a table that contains entries for both employees and prospective
employees, it might be desirable to use SSN to identify such people, and
that could serve as a reference to a person of either kind.

There are performance implications to all of this, but that's secondary to
the fundamentals of data management, to which I refer you again.

Please read a book on RDBMS so you now the concepts.

Yes. One identifier, one place, one time, the references to it.
Please, please read at least one book on the basics of RDBMS.

On 12 Sept., 10:52, filipe.martins... (AT) gmail (DOT) com wrote:
If you are speaking only about naming conventions then my version of
them you can found here
Naming conventions of tables, columns, indexes, keys, sequences in
Oracle at http://www.gplivna.eu/papers/naming_conventions.htm

It includes also how to name foreign key columns as well as the very
foreign key constraints.

Gints Plivna
http://www.gplivna.eu