Will this Database design logic work

database-design

I am currently working on structuring out a database in MySQL Workbench to keep track of expenses for Truck drivers that would be turned into an application using Java.

  • Objective:

    Create a report of expenses in a table format
View or Edit past expenses
Display data Yearly report
        Total Company Salary
        Total Gas Expense
        Total Toll Expense
        Total Miscellaneous Expense

  • Input Needed:

    Enter the Company Income earned weekly
Enter the Gas Expense weekly
Enter Toll Expense Weekly
Enter Miscellaneous expense weekly
        Create a Miscellaneous category

  • Calculation:

    Get Personal Salary = 
        Company Income - (Gas Expense + Toll Expense + Miscellaneous Expense)

  • Result:

    Display information in a table

Right now the issue is structuring out MySQL tables.
Currently I have:
enter image description here


The portion of the results and calculations will be done in Java but I would like to know if this table structure is correct? I also want the user to input the type of miscellaneous expense.

Best Answer

There are a couple of problems with this design.

  1. Your link between users and expenses is around the wrong way. As it is designed, a single expense record can have multiple users. This should be a single user can have multiple expense records. Remove expense_id from users and add user_id to expenses (with related foreign keys).
  2. You're going to end up with many NULL values in your expenses table and it will be a pain to query for analysis because of this, you will end up having to coalesce fields for any analysis of total expenses rather than individual expense types. Instead of having separate columns for the gas expense, toll expense and misc expense, have a single expense_value field and create an expense_type field linking to an expense_type lookup table that contains gas, toll and miscellaneous as records
  3. Your personal_salary field should not be an attribute of an expense record, it should be an attribute of the user record. Again, you'll be storing this data repeatedly for no reason. Imagine if you were asked to provide a report on personal salaries only - do you want to query thousands of expense records or a few dozen employee records?
  4. As above but with company_income, this should be an attribute of a company record, not an expense record.

NOTE: With points 3 & 4 above, if personal salary and company income are not annual/on-going static values but are instead values for a specific project or contract then these should still not be stored with expenses. Create a new table for Project/Contract, add a foreign key referencing a project/contract to the expense table and do it that way.

Also, consider your application design. Doing aggregations and calculations across small data sets in Java is fine, but if you're talking about producing annual reports and your data set grows large, this processing would probably be best handled by the database engine which can handle this work in a set-based method rather than row-by-row processing as happens in most applications.

Related Question