2

I have an expense management system for monthly expenses. Expenses are of two types: one-time monthly expenses, and daily expenses for each day of month. I have created the following tables:

setupExpenses (ExpenseID, Title, IsDaily)
ExpenseMaster (ExpenseMasterID, Month, EmployeeID)
ExpenseDetails (ExpDetID, ExpenseMasterID, ExpenseID, Date, Amount)
ExpenseDetailsDaily(ID, ExpDetID, Date, Amount)

if isdaily is true in setupExpenses then an entry is made in the 3rd table otherwise I have entries in the other two transaction tables. Any better suggesstion is appreciated. This design doesn't feel right to me.

Improve this question
3
  • You might get more expense types in the future. You could get weekly, every second Tuesday, semi-annual, etc. Keep that in mind as you look at your design.
    – Dan Bracuk
    Jun 17, 2013 at 12:13
  • yes thats is one of the reason i m looking for better option
    – Zia
    Jun 17, 2013 at 12:15
  • Why aren't all expense details in ExpenseDetails? It seems odd to have an expense detail with a date that is effectively a master for daily details with their own dates. Is there also a category or account that might be helpful in indicating which details only occur once per month? When you populate a new month from setupExpenses do you generate rows for all of the days in the month (for IsDaily expenses) with the Amount set to zero?
    – HABO
    Jun 17, 2013 at 12:34

3 Answers 3

Reset to default
3

Without knowing more about the business requirements, with the given structure you defined I would probably go with something like this:

Expense (Id, DetId, TypeId, Amount, Date, EmployeeId)
ExpenseType (Id, Name)

Where Expense.TypeId is a foreign key constraint to ExpenseType.Id. ExpenseType could contain rows for daily or monthly expenses. This is scalable, as if down the road you have the requirement for weekly expenses, or yearly expenses (etc.) then this normalization will benefit. Expense.EmployeeId would be a foreign key constraint to your employee table, and Expense.DetId sounds like it'd be a foreign key constraint to another table that I can't seem to translate from the column name. If it is just details for each expense, no reason not to just put it directly in the Expense table (provided a 1:1 relationship between expense and expense details). I may be missing a few columns, but I'm sure you get the idea.

Improve this answer
0

The first table needs a more consistent name. Also, table names are usually singular.

ExpenseSetup
------------
ExpenseSetupID
Title
ExpenseOccurrence


ExpenseMaster
-------------
ExpenseMasterID
EmployeeID
ExpenseSetupID


ExpenseDetail
-------------
ExpenseDetailID
ExpenseMasterID
ExpenseDate
Amount

I don't understand why the daily expenses have to go in a separate table. You can get the expense occurrence by joining back to the ExpenseSetup table.

Improve this answer
0

You can try the following scehema, it would avoid 1 table and the repetitive data 

ExpenseMaster (ExpenseMasterID, Month, EmployeeID, IsDaily)
ExpenseDetails (ExpDetID, ExpenseMasterID, Date, Amount)
ExpenseDetailsDaily(ID, ExpDetID, Date, Amount)
Improve this answer
1
  • Expense head need to be defined in advance I can't avoid that table. On monthly basis employee will enter amounts for them
    – Zia
    Jun 17, 2013 at 12:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.