I have building a sales commissioning application using Ruby On Rails. The application has three entities. Sales agent, banks and insurance companies. The sales guy can get commission from either bank or insurance or both based on a monthly or weekly fixed amount or based on percentage of the sold amount.
I am confused with the database design. I took a look on this post but I am not convinced. Database Design - sales from multiple sources
Thanks in advance.
I'm guessing that any individual commission can come from either a bank or an insurance company, but not both. There are at least two ways to handle this:
5-table design: Agent, Bank, Insurance, BankCommission, InsuranceCommission. Bank Commission references Bank, similar for Insurance Commission.
4-table design: Agent, Bank, Insurance, Commission. Commission references either Bank or Insurance (two fields, exactly one not null).
The choice which one to use depends largely on what the two types of commission look like. If they are identical except for the source of commission, go with design 2. That way you save yourself repetitive work, both in creating/modifying the schema and query writing.
However, if commissions from banks and insurances differ (have several different values to be stored - either now or if you anticipate this will happen in the future), go with design 1. Your queries will likely need to handle the commission types differently anyway, and design 2 will generally become more complex.
If in doubt, I'd pick design 1. More robust towards future changes, and likely a little more efficient for querying as well - although that will depend on the query. Another potential trip-up with the 4-table design is that you'd need to specify foreign keys over columns where the source column may contain null values. The way how to handle such foreign keys correctly differs between database vendors coughSQLServercough, so you lose some portability.