0

This isn't exactly related to modeling the database but rather about "standardizing" column placement.

Where should I generally put my FK column? As the last column of the table? At the start just after the primary identifier? After a relevant set of columns in the middle of the table?

Are there any industry standards I can hold on to, to make my life easier?

If it means anything, I'm using mysql, but this is a general db design question.

closed as primarily opinion-based by a_horse_with_no_name, RolandoMySQLDBA, Paul White, dezso, Mark Sinkinson Jul 8 '15 at 8:06

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    The order of columns in a relational table is irrelevant. – a_horse_with_no_name Jul 6 '15 at 13:41
  • I'm aware it has no performance or functionality impacts, but just for the sake of being standardized. – matteeyah Jul 6 '15 at 13:46
  • 1
    This is all about coding standards. The usual answer is: pick one and stick to it. – a_horse_with_no_name Jul 6 '15 at 13:47
0

Although inherent column order does not matter, but since you're looking for 'industry standards', you can search publicly available databases, like Microsoft's AdventureWorks, to see where Foreign Key columns are placed.
It's usually at the beginning.

This MSSQL query lists all FKs in a database :

SELECT
    FK_Table = FK.TABLE_NAME,
    FK_Column = CU.COLUMN_NAME,
    PK_Table = PK.TABLE_NAME,
    PK_Column = PT.COLUMN_NAME,
    Constraint_Name = C.CONSTRAINT_NAME
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
            SELECT
                i1.TABLE_NAME,
                i2.COLUMN_NAME
            FROM
                INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            WHERE
                i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
           ) PT
    ON PT.TABLE_NAME = PK.TABLE_NAME
0

In MySQL, there is no benefit of having one ordering of columns over another. Do what "feels good" to you.

I have heard of no general standard. However most (not all) tables I see have the PRIMARY KEY as the first column(s). I see no pattern for FOREIGN KEYs.

Not the answer you're looking for? Browse other questions tagged or ask your own question.