"RR" <te**********@y ahoo.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(Employ eeID),
(TransID) REFERENCES Transaction(Tra nsactionID));
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.