Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  Database Design and Application Architecture  Expense Database with Mileage Rate

Author  Topic  marly Starting Member 6 Posts Posted - 2008-07-17 : 16:04:30 I’m designing a simple expense database (and Service Report DB), for myself using MS-SQL Server 2005. My table layout so far is like this: Company CompanyID, CompanyName, Phone Number, Address, City, State, Zip Contact ContactID, CompanyID, FirstName, LastName, EmailAddress, etc. Employee EmployeeID, ExpenseID, FirstName, LastName, Address, etc. Expense ExpenseID, ExpeneseDate, Miles, Toll, Parking, etc. The part where I really don’t know what to do with is the IRS Mileage Rates, for example, from Jan 1, 2008 through June 31st 2008, it’s 50.5 cents per mile. Starting on July 1st, through December 31st it’s 58.5 per mile. I was thinking of putting in a lookup table like this: MileageRates MileageRateID, StartDate, EndDate, Rate Then adding in a MileageRateID to my Expense table, that way I can select the rate, or I probably don’t even need to add in the MilerageRateID, since I can check if the expense date is between these two dates, then it’s this rate, if it’s between these two dates then it’s this rate. Of course, I could just put in a MileageRate into the Expense table and enter it in, but then that would be a lot of duplication of the same thing. The question really is, what would be the best way to go about that? Thanks, marly