ThomasRushton - Monday, January 8, 2018 2:15 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?

Two things come to mind...

(1) You'll fail if you try to do that with a CHAR(10) - "Credit Note" is 11 characters, which leads on to
(2) If you do need to create a longer description than the field will accept, you'll need to change a potentially very large table to do it, rather than quickly change a small lookup table.