Best Practice for Foreign Key column position in table

Question

This might be a silly question, but here goes:

Is there a standard or best practice, which specifies in what order the foreign key columns in a table should?

I for one like the idea of the PK being the very first column in the table, followed by all the foreign keys, and then the columns thats relevant to that table..

Other way of doing it is having the PK as the very first column, then all the supporting columns, and then all foreign keys...

I guess it really doesn't matter, but I would like to get a standard across my organisation for this...

Answer 1

Doesnt matter in the slightest, i think what matters most is how you name the columns. Ensuring that the foreign keys all follow some sort of convention will make your life easier while developing.

eg adding a _fk suffix to all foreign keys or something similar.

So vehicle_id becomes vehice_id_fk when used as a foreign key.

Answer 2

Where I've worked, I've seen PK first, followed by foreign keys, then data. But DRL is right; it doesn't matter. The order in which you specify multi-column indexes is critical, but not the order in which you specify columns in the table. If you suffix foreign-key column names with _fk or _key or _id or a similar convention it'll save time for you and your successors.

Answer 3

Agreed with DRL. A convention based on column ordering would be hard to maintain as new columns were added. Plus, most db clients will be able to give you a list of columns and their designations (PK, FK etc.).

Answer 4

Interesting idea, but personally I think that having "a standard across my organisation" for this would do more harm than good.

Training and best practices sound fine, but a blindly enforced rule is "considered harmful".

Answer 5

Better not to have such a rule, I would suggest. 'Rules are for fools.'

What would happen if you did introduce such a rule, and someone wanted to add a new key? Or add a new column to an existing key? Are they supposed to rearrange all the columns to do so? I can't see what you'd gain from that.

Your columns should, ideally, be named so it's clear what is in them. That isn't easy, and a little extra thought will repay itself in the long term.

Answer 6

The order of foreign key columns in a table isn't, and shouldn't be relevant. The best standard I've seen (that degrades over time, naturally, is):

1. Primary key columns
2. NOT NULL columns that are of "fixed" length and that aren't likely to change.
3. NOT NULL columns that are likely to be populated via update
4. NULLable columns that are likely to be populated via update
5. Columns, nullable, or no, that almost always change. (Think of a MOD_TIMESTAMP column which is used for stateless optimistic locking.)

The goal of such an implementation is to the amount of required logging on update.

From a presentation perspective, a decent ERD tool should present foreign key references at both a column and "line" level.

Also, I'd not follow the advice I've seen earlier - e.g. VEHICLE_ID_FK. Just put VEHICLE_ID in the child table of the VEHICLE table and move on. If you need multiple VEHICLE_ID columns, typecast them appropriately, like COPS_VEHICLE_ID and ROBBERS_VEHICLE_ID.