I am confused. So, there should be no hard-coding. Right? Either ID or name will be a variable or defined in a configuration table or file.
Performance wise, you would want to go the ID which should be unique and indexed. -----Original Message----- From: Ryan Whitney [mailto:[email protected]] Sent: Sunday, March 08, 2009 4:06 PM To: [email protected] Subject: [Mifos-users] Best Practices: Using IDs in Querys for Reports? Hi All, Working on some reports and wondering about a best practice. For instance, I'm working on this query to find disbursement fees attached to a loan. Here's the query I came up with SELECT account_fees.ACCOUNT_FEE_AMNT FROM account_fees INNER JOIN fees USING (FEE_ID) INNER JOIN fee_frequency USING (FEE_ID) WHERE fee_frequency.FREQUENCY_PAYMENT_ID = 2 AND account_fees.ACCOUNT_ID = ? My question is about the fee_frequency.FREQUENCY_PAYMENT_ID. If I hard-code in the ID value, I save having to reference two tables. The other way I could do it is putting in the name (ignoring the mis-spelling of disbursement): SELECT account_fees.ACCOUNT_FEE_AMNT FROM account_fees INNER JOIN fees USING (FEE_ID) INNER JOIN fee_frequency USING (FEE_ID) INNER JOIN fee_payment ON fee_frequency.FREQUENCY_PAYMENT_ID = fee_payment.FEE_PAYMENT_ID INNER JOIN lookup_value ON fee_payment.FEE_PAYMENT_LOOKUP_ID = lookup_value.LOOKUP_ID WHERE lookup_value.LOOKUP_NAME = 'FeePayment-TimeOfDisburstment' AND account_fees.ACCOUNT_ID = ? For some reason, I keep thinking it's important to put in the longer query to get the name, because the ID might change? Is this the right approach or should I go with the ID? If it is the right approach, what's the explanation why? Thanks! Ryan -- Ryan Whitney Mifos Technical Program Manager [email protected] Mifos - Technology that Empowers Microfinance (www.mifos.org) ------------------------------------------------------------------------ ------ Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA -OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise -Strategies to boost innovation and cut costs with open source participation -Receive a $600 discount off the registration fee with the source code: SFAD http://p.sf.net/sfu/XcvMzF8H _______________________________________________ Mifos-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/mifos-users ------------------------------------------------------------------------------ Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA -OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise -Strategies to boost innovation and cut costs with open source participation -Receive a $600 discount off the registration fee with the source code: SFAD http://p.sf.net/sfu/XcvMzF8H _______________________________________________ Mifos-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/mifos-users
