employee commissions

[RR]

I am wondering what tables and relationships I would have to have to keep
track of one or several employees commissions per transaction.

One tracsaction can have one or several employees work on it, and the
commissions for each employee depends on how many employees worked on it.

Thanks

[Squirrel]

Hi,

I'd suggest considering something like this:

Employee table with EmployeeID as primary key and EmployeeName etc.
Transaction table with TransactionID as primary key and EmployeeID as
foreign key.

The question arises with regard how to treat the commissions. If the
commission is calculated based on the number of employees working on the
transaction then this sounds like calculated data, i.e. unknown until all
the relevant employeeIDs have been entered. If you need the commission
amount for queries and reports then it can be a calculated value rather than
a field stored in the database. If you must store it in the database then I
think you need an update query to update the commission amounts after all
the employeeIDs have been entered - this assumes the commission is shared
equally between the employees.

HTH -Linda

"RR" <te**********@yahoo.com> wrote in message
news:cp*****************@fe2.texas.rr.com...

I am wondering what tables and relationships I would have to have to keep
track of one or several employees commissions per transaction.

One tracsaction can have one or several employees work on it, and the
commissions for each employee depends on how many employees worked on it.

Thanks

[Pieter Linden]

"RR" <te**********@yahoo.com> wrote in message news:<cp*****************@fe2.texas.rr.com>...

I am wondering what tables and relationships I would have to have to keep
track of one or several employees commissions per transaction.

One tracsaction can have one or several employees work on it, and the
commissions for each employee depends on how many employees worked on it.

Thanks

Employee---(M,M)---Transaction

So...

Employee--(1,M)---WorksOn---(M,1)---Transaction

So...

CREATE TABLE Employee(
EmployeeID Autonumber PRIMARY KEY,
FirstName Text(25) NOT NULL,
LastName Text(25) NOT NULL,
....
);

CREATE TABLE Transaction(
TransactionID Autonumber PRIMARY KEY,
....);

CREATE TABLE WorksOn(
EmpID LONG INTEGER,
TransID LONG INTEGER,
PRIMARY KEY (EmpID, TransID)
FOREIGN KEY (EmpID) REFERENCES Employee(EmployeeID),
(TransID) REFERENCES Transaction(TransactionID));

And then the amount of the commission each gets is 1/n * value of
commission. Where n is the count of values in WorksOn for the given
TransID.