FK'd column naming best practice

[brentnicholas]

Hey all, I don't really need a solution, so much as just interested in people's best practice advice.

Normally when I create a table and have another table (lookup table) I'll do my FK's as follows:

CREATE TABLE PVHIST_SCR13 (
PVHIST_SCR13_SEQ NUMBER NOT NULL
,TYPE NUMBER
,BINDER VARCHAR2(1)
,ADDITIVE NUMBER
,AGGREGATE VARCHAR2(1)
,SUPPLIER NUMBER
,CREATED_DATE DATE DEFAULT SYSDATE NOT NULL
);

Lookup table:
CREATE TABLE PVHIST_SCR13_ADDITIVE (
PVHIST_SCR13_ADDITIVE_SEQ NUMBER NOT NULL
,ADDITIVE VARCHAR2(20)
,CREATED_DATE DATE DEFAULT SYSDATE NOT NULL
);

Normally I FK the TABLE PVHIST_SCR13_ADDITIVE table back to PVHIST_SC13, like this PVHIST_SCR13_ADDITIVE_SEQ = PVHIST_SCR13_ADDITIVE_SEQ.

However this time I did this ADDITIVE = PVHIST_SCR13_ADDITIVE_SEQ.
Note, that not proper syntax of course, just for illustration.

Is this a bad idea? On the one hand I like the fact I can look at the main table and see 'ADDITIVE' and know what it is, on the other hand using PVHIST_SCR13_ADDITIVE_SEQ in both places as the column name is very clear with out having to look up table contraints.

Thoughts? Am I just splitting hairs?
There is a good reason I'm asking this but don't feel like writing several pages about what I'm doing...

Thanks for your time!
BN

[Twey]

PVHIST_SCR13_ADDITIVE_SEQ doesn't seem very self-explanatory to me. I usually use <tablename>_id for the names of both my ID columns and the foreign keys that link to them — I find it avoids confusion in the long run. One convention that does apply is that identifiers, including column names and table names, should be lower-case. This is done to distinguish them from keywords in queries, and because certain database systems treat case oddly (and differently depending on the platform, in the case of one notable).g