Sql-server – Database design for conditional detail table

database-designnormalizationschemasql server

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.

Best Answer

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.

Related Solutions

How to design a schema to store search keywords

First, let me address your concern about data redundancy. I agree with you that the second schema is more likely to reduce the redundancy, and is probably closer to what I would go with. One thing to be aware of, though, is this will be unique search terms. So unless you have some way of normalizing data, misspellings will get through and be viewed as different search terms.

Also, I would switch the way you link the searchTerm to the date searched, so that dateofSearchTerm links to the search term (not the other way around).

CREATE TABLE SearchTerm (
 ID             INTEGER         PRIMARY KEY AUTOINCREMENT NOT NULL,
 SearchTerm     VARCHAR( 255 )  NOT NULL,
 Hit           INTEGER         NOT NULL,
);


CREATE TABLE DateofSearchTerm (
 DateID         INTEGER         PRIMARY KEY AUTOINCREMENT NOT NULL,
 searchID       INTEGER         NOT NULL,
 Date           DATE            NOT NULL
);

It would also be a good idea to make searchID a foreign key that references searchTerm.ID to ensure there are no orphan rows (rows in DateofSearchTerm that do not have a matching parent in SearchTerm).

As for your anecdotal story about your friend, the biggest thing I see you missing in your designs are indexes. A table 3-million strong should have no problem running quickly with proper indexes.

Using your design, you would want an index on the searchTerm.searchTerm column, and on DateofSearchTerm.date.

I think if you follow the advice to make searchID a foreign key, that will also become a proper index. I don't use SQL Server, so I don't know if foreign keys require an index, so make sure that it does.

Billing Module (Fee) for School Management System

In addition to seconding Joel Brown's comment I would like to suggest a couple things to help.

If this is for internal use only start with open source accounting and ERP db's. For example, LedgerSMB or Adempiere. Also do this if you are releasing open source. If you are not releasing open source, consider interoperating with such software for billing. I would be happy to help (conflict of interest alert).
Look specifically at batch processing and review requirements. This allows human review as to how much money is owed before the money hits the books.

One thing we are doing in LedgerSMB is to have the possibility of "template transactions" which can then be copied out and filled in (possibly by automatic processes).

Accounting is a large field but if you are handling billing you really need to know it.

Along these lines, I don't really see anything wrong with what you have posted but it is quite incomplete. You need line items for the invoices, and you need line items for the payments. In this way it becomes a lot simpler to track how much is owed. You may also want to separate out students from customers in case one parent has multiple children enrolled in the school.

Best Answer

Related Solutions

How to design a schema to store search keywords

Billing Module (Fee) for School Management System

Related Question