Same table structure with different name and different use - SQL

Question

For a better understanding of the problem, let us consider 2 simplified tables, allowance and deduction :

CREATE TABLE Allowance
(
    [Id]        INT             NOT NULL IDENTITY PRIMARY KEY,
    [Label]     VARCHAR(100)    NOT NULL UNIQUE,
    [Amount]    DECIMAL(20,3)   NOT NULL
)

and

CREATE TABLE Deduction
(
    [Id]        INT             NOT NULL IDENTITY PRIMARY KEY,
    [Label]     VARCHAR(100)    NOT NULL UNIQUE,
    [Amount]    DECIMAL(20,3)   NOT NULL
)

I am currenty working on a payroll software, and I believe the 2 tables are self-explicit and does not require further explanations. As you can see both tables have the same structure but are have different business logic. One adds to the basic salary and the other deducts.

Here is my question. Is it better to seperate the tables by business logic, as it is actually, or combine them in one table and add a new column flag, denoting the nature of a row:

• 0 for allowance
• 1 for deduction

A comparison between those two concepts would be most welcome.

Answer 1

It is a matter of how the data relates--if there is no true relationship in the two tables, they should be kept separate. But, if you would need to work with the data together, such as a single SUM of both Allowances and Deductions, then combining into one table might make more sense. Without knowing more of your design plans, it is not possible to make a good recommendation.