Table income and expense is better to have it together or apart

database-design

I am developing a small system where I have to record incomes and expenses of the company, and even I can not determine if the income and expense table should be in one table or in separate tables.

The design of the database must allow the system to generate a report of income and expenses by date (in column incomes and other expenses).

I would like to explain me what would be the advantage or disadvantage of having them together or separately

Best Answer

If you consider income as a negative expense, and have categorised it sufficiently, a single table is just fine. The columns describing an income transaction are essentially identical to the columns describing an expense.

You can easily have indexes that allow you to quickly find your income or expenses, based on your categories (because negative income is not necessarily an expense, you should have things categorised appropriately). And with an index on time you can easily pull up your change over time.

If you're doing double-entry accounting, you should be able to easily add up your transactions and see they always add to zero. It's a useful test for consistency. Then some reports will leave out "equity" lines to compare Profit and Loss, etc.

If you use separate tables, you can use UNION ALL queries to query across the lot, but I would consider this extra effort you don't really need.

Related Solutions

Mysql – Two nullable columns one required to have value

A version of JD Schmidt's answer, but without the awkwardness of an extra column:

CREATE TABLE foo (
  FieldA INT,
  FieldB INT
);

DELIMITER //
CREATE TRIGGER InsertFieldABNotNull BEFORE INSERT ON foo
FOR EACH ROW BEGIN
  IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'FieldA\' and \'FieldB\' cannot both be null';
  END IF;
END//
CREATE TRIGGER UpdateFieldABNotNull BEFORE UPDATE ON foo
FOR EACH ROW BEGIN
  IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'FieldA\' and \'FieldB\' cannot both be null';
  END IF;
END//
DELIMITER ;

INSERT INTO foo (FieldA, FieldB) VALUES (NULL, 10); -- OK
INSERT INTO foo (FieldA, FieldB) VALUES (10, NULL); -- OK
INSERT INTO foo (FieldA, FieldB) VALUES (NULL, NULL); -- gives error
UPDATE foo SET FieldA = NULL; -- gives error

Sql-server – Database design for conditional detail table

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.

Best Answer

Related Solutions

Mysql – Two nullable columns one required to have value

Sql-server – Database design for conditional detail table

Related Question