Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 '13 at 12:13
    
yes thats is one of the reason i m looking for better option –  Zia Jun 17 '13 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 '13 at 12:34

3 Answers 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.

share|improve this answer

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.

share|improve this answer

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)
share|improve this answer
    
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 '13 at 12:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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