Database Design Question

I know this isn't SQL Server specific but I've been pondering over
designing a payment system for our existing application which uses SQL
Server 2000.

We have an application that tracks employees and the companies that
they consult for. The employees are in an employee table using an
identity column for the primary key called EmployeeID. The companies
are in a company table, also using an identity column for its PK
called CompanyID.

The employees are contracted out to different companies. Each company
has different responsibilities for paying some of the wages and
benefits of our consultants based on the contract with the employer.

Here are the 4 possible scenarios we need to support:
1) Employee makes payment to a Company's account (e.g. reimbursing
company for personal phone calls done on company equipment, broken
equipment, etc)
2) Employee makes payment to its own account (e.g. personal
contribution to retirement fund, health insurance not covered by
company contract, etc)
3) Company makes payment to an Employee's account (e.g. health
insurance, pension, dental, overtime wages, reimbursements for
personal expenses, etc)
4) Company makes payment to its own account (e.g. consulting fees
charged by our consulting firm, other services our firm provides the
company)

A payment can be broken down into the following elements:
1) Identity column for PK
2) The date/time of the payment
3) Who made the payment (Employee or Company)
4) To which account does the payment go towards (Employee or Company)
5) User who entered the payment into the system (for audit)

A second table is needed to show the details of the payment since the
amounts can go to multiple payment categories (e.g. $50 for Health
Insurance, $75 for retirement fund, $10 for Dental, etc):
1) Identity column for PK
2) PaymentID (FK to link to payment table above)
3) PaymentCategoryID (FK to link to the payment category)
4) Amount

The question lies in how to handle the relationship of the employee
table to the payment table and of the company table to the payment
table. What options do I have when designing this?

The ideal solution would provide referential integrity via the DBMS,
to prevent deleting an employee who made a payment or received money
and to prevent deleting a company who made a payment or received money
in its account.

ThanksOn 10 Sep 2004 08:19:01 -0700, cyansoft wrote:

>I know this isn't SQL Server specific but I've been pondering over
>designing a payment system for our existing application which uses SQL
>Server 2000.
>We have an application that tracks employees and the companies that
>they consult for. The employees are in an employee table using an
>identity column for the primary key called EmployeeID. The companies
>are in a company table, also using an identity column for its PK
>called CompanyID.
>The employees are contracted out to different companies. Each company
>has different responsibilities for paying some of the wages and
>benefits of our consultants based on the contract with the employer.
>Here are the 4 possible scenarios we need to support:
>1) Employee makes payment to a Company's account (e.g. reimbursing
>company for personal phone calls done on company equipment, broken
>equipment, etc)
>2) Employee makes payment to its own account (e.g. personal
>contribution to retirement fund, health insurance not covered by
>company contract, etc)
>3) Company makes payment to an Employee's account (e.g. health
>insurance, pension, dental, overtime wages, reimbursements for
>personal expenses, etc)
>4) Company makes payment to its own account (e.g. consulting fees
>charged by our consulting firm, other services our firm provides the
>company)
>A payment can be broken down into the following elements:
>1) Identity column for PK
>2) The date/time of the payment
>3) Who made the payment (Employee or Company)
>4) To which account does the payment go towards (Employee or Company)
>5) User who entered the payment into the system (for audit)
>A second table is needed to show the details of the payment since the
>amounts can go to multiple payment categories (e.g. $50 for Health
>Insurance, $75 for retirement fund, $10 for Dental, etc):
>1) Identity column for PK
>2) PaymentID (FK to link to payment table above)
>3) PaymentCategoryID (FK to link to the payment category)
>4) Amount
>The question lies in how to handle the relationship of the employee
>table to the payment table and of the company table to the payment
>table. What options do I have when designing this?
>The ideal solution would provide referential integrity via the DBMS,
>to prevent deleting an employee who made a payment or received money
>and to prevent deleting a company who made a payment or received money
>in its account.
>Thanks

Hi Cyansoft,

My tables would probably look something like this:

CREATE TABLE Payments
(PaymentID int NOT NULL IDENTITY,
PayDateTime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PaidByEmp int,
PaidByComp int,
PaidToEmp int,
PaidToComp int,
EnteredBy nvarchar(256) NOT NULL,
PRIMARY KEY (PaymentID),
FOREIGN KEY (PaidByEmp) REFERENCES Employees (EmpID),
FOREIGN KEY (PaidByComp) REFERENCES Companies (CompID),
FOREIGN KEY (PaidToEmp) REFERENCES Employees (EmpID),
FOREIGN KEY (PaidToComp) REFERENCES Companies (CompID),
CHECK ((PaidByEmp IS NULL AND PaidByComp IS NOT NULL)
OR (PaidByEmp IS NOT NULL AND PaidByComp IS NULL)),
CHECK ((PaidToEmp IS NULL AND PaidToComp IS NOT NULL)
OR (PaidToEmp IS NOT NULL AND PaidToComp IS NULL))
)

CREATE TABLE PaymentDetails
(PaymentID int NOT NULL,
PaymentCategoryID int NOT NULL,
Amount decimal(8,2) NOT NULL,
PRIMARY KEY (PaymentID, PaymentCategoryID),
FOREIGN KEY (PaymentID) REFERENCES Payments (PaymentID),
FOREIGN KEY (PaymentCategoryID) REFERENCS Categories (CatID)
)

Note that I left out the extra identity column you planned for the details
table; the natural key (payment + category) is just fine in this case.

I'd also try to find out what the natural key for a payment is. I think
the bookkeeping department should be able to fill me in on that. Than I
would add those columns to the table and define a UNIQUE constraint
forthem; I'd also conder using it instead of the identity column. That
would depend on the number of columns and kind of data in the natural key
(as it would propagate to the details table, I'd stick with identity if
the natural key is too cumbersome).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||cyansoft (cyansoft@.hotmail.com) writes:
> A payment can be broken down into the following elements:
> 1) Identity column for PK
> 2) The date/time of the payment
> 3) Who made the payment (Employee or Company)
> 4) To which account does the payment go towards (Employee or Company)
> 5) User who entered the payment into the system (for audit)
> The question lies in how to handle the relationship of the employee
> table to the payment table and of the company table to the payment
> table. What options do I have when designing this?

You could have a Payers table, which would be a supertable of Employees
and Companies. My personal choice, though, would be to have one EmployeeID
and one CompanyID column, and this constraint:

CHECK (EmployeeID IS NULL AND CompanyID IS NOT NULL OR
EmployeeID IS NOT NULL AND CompanyID IS NULL)

That is, requiring that exactly one of the columns should be non-NULL.
Referential integrity is now trivial.

> A second table is needed to show the details of the payment since the
> amounts can go to multiple payment categories (e.g. $50 for Health
> Insurance, $75 for retirement fund, $10 for Dental, etc):
> 1) Identity column for PK
> 2) PaymentID (FK to link to payment table above)
> 3) PaymentCategoryID (FK to link to the payment category)
> 4) Amount

There is no need for an IDETNITY column here as I can see. (PaymentID,
PaymentCategoryID) appears to be a natural primary key to me.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9560F3C3A2761Yazorman@.127.0.0.1>...
> You could have a Payers table, which would be a supertable of Employees
> and Companies. My personal choice, though, would be to have one EmployeeID
> and one CompanyID column, and this constraint:

Pardon my ignorance(since Googling came up with a bunch of stuff about
Informix), but what exactly is a supertable and why do you prefer
using the check constraint over a supertable in my situation? In what
situation(s) would a supertable be preferred? How would one implement
a supertable in SQL Server 2000?

Thanks|||cyansoft (cyansoft@.hotmail.com) writes:
> Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9560F3C3A2761Yazorman@.127.0.0.1>...
>> You could have a Payers table, which would be a supertable of Employees
>> and Companies. My personal choice, though, would be to have one
>> EmployeeID and one CompanyID column, and this constraint:
> Pardon my ignorance(since Googling came up with a bunch of stuff about
> Informix), but what exactly is a supertable

A supertable in this context could be called PaymentActors, and the
Employees and Companies tables would both have their PKs as foreign
keys to this table.

> and why do you prefer using the check constraint over a supertable in my
> situation?

Because I feel like it! :-)

Seriously, it is not a simple question, because it is a trade-off. Let's
say that you would have umpteen tables where the entity could be a
company or an employee. Then I would do it. But if it is a one-off, I
prefer to have two columns.

> In what situation(s) would a supertable be preferred? How would one
> implement a supertable in SQL Server 2000?

Check this link for an example from Joe Celko:
http://groups.google.com/groups?ie=...om%3E&lr=&hl=sv

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp