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.

| improve this 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
| improve this answer | | | | |
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.

| improve this answer | | | | |

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