Home Forums Database Design Relational Theory Is My Foreign Key Table Unnecessary? RE: Is My Foreign Key Table Unnecessary?

  • January 3, 2018 at 3:47 am

    #1974177

    Grant Fritchey - Tuesday, January 2, 2018 8:33 AM

    Phil Stratford - Tuesday, January 2, 2018 8:12 AM

    I'm sure this has been addressed many times here but none of the search terms I've tried have surfaced anything relevant.

    Simply, I have a SalesLedgerTransaction table. One of the columns is SalesLedgerTransactionTypeID which is a Foreign Key column to to the SalesLedgerTransactionType table. That table only has two columns - ID and Description - and will only ever contain five records, with values in the Description column of 'Invoice', 'Payment', 'Refund', 'Write-Off' and 'Credit Note'.

    I'm wondering whether this is a pointless use of a Foreign Key column. I could easily replace it (we're still in the early stages of development) with a CHAR(10) column and just write 'Invoice', 'Payment', etc. to that column, then drop the SalesLedgerTransactionType table altogether. The value will only ever be populated by the application's code, never by the user, so there's no potential for invalid values to be entered (barring developer errors in the code, I realise - we could always add a constraint on the column to restrict the permissible values, if necessary).

    As far as I can tell, the only advantage that the Foreign Key column and table are giving me is that storing a single-digit integer takes up less disk space than the word 'Invoice' or 'Payment' over and over again, but given the size of the user base and the business that is going to be a very small difference in actual bytes.

    Is there anything I'm missing, or any strong case to be made for one solution or the other?

    Sure sounds like it's needed. Today there will only, ever, be five values. Tomorrow, totally different world. Also, what happens as the code changes? I don't think about referential integrity as only enforcing constraints. It's also a backup for the code. Sure, the code can do this and do it well. However, as we all know, it can get messed up. Why not have the backup in place to ensure appropriate data is always entered correctly, no errors, no slip-ups.

    You're absolutely right that a sixth, seventh or even eighth value could be required in the future, but if that happened we could simply start writing the new value to the CHAR(10) column. However, I take the broader point that you're making and I agree that the foreign key table is the best way to go. Thanks!