By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,946 Members | 709 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,946 IT Pros & Developers. It's quick & easy.

employee commissions

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
"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.
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.