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 that describes the order of foreign key column in a mssql table?

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 all other columns thats relevant to that table.

Another 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.

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.