Admin

I have a table in Microsoft Access 2002 (though I don't believe my question is specific to Access) where the idea of a "one-to-many relationship" in two tables was smushed into that one table and the "many" part of was limited to 4 on. I suspect there is a name for this kind of table?

CONTRACTS: EMPLOYEE1, EMPLOYEE1_BONUS, EMPLOYEE2, EMPLOYEE2_BONUS, EMPLOYEE3, EMPLOYEE3_BONUS, EMPLOYEE4, EMPLOYEE4_BONUS,DATE-STUFF

The idea behind the table is that in a given contract, up to four employees can be involved, but "Fred" may be listed as EMPLOYEE1, EMPLOYEE2, etc. depending on the data-entry person.

Well, now I want to write an mssql query (in Access with access tables) to show me some things that seem tricky given the lack of a one-to-many relationship--specifically, I need to show how many contracts a given employee had throughout the year. But the employee's name is either EMPLOYEE1, EMPLOYEE2, EMPLOYEE3, OR EMPLOYEE4. Same problem with calculating how much bonus each one earned: four potential fields. So, should I restructure the database for a one-to-many relationship (the schema is simple enough to take a day or two), or am I overlooking some cool querying tricks?


solution:

Change that schema right away!



Seriously, you are seeing for yourself what that was a bad design, and you should get it changed ASAP. Before you know
it, someone will mention that they started using a fifth person on some jobs...

and

The person who designed the database had no idea what he was doing and missed the whole concept of Relational Databases.

Yes, redesign the database immediately to have at least 2 tables:

Employees and Contracts and there will be a PrimaryKey-ForeignKey relationship between Employees and Contracts
on EmployeeID. Contracts table will contain a PrimaryKey named ContractID and other contract information you want to store. Employees table will contain a PrimaryKey named EmployeeID and other employee information you want to store. If the bonus is on a contract basis it should be stored in the Contracts table, if not you can have another table for Bonuses which will contain EmployeeID, ContractID, Bonus and other bonus information you would like to store.

also

Right. You need to make a new table that lists each employee and bonus as an individual record. Suppose you call the new table EmpBonus. I will also assume that employees and contracts are uniquely identified by integers.

CREATE TABLE EmpBonus (
ContractID INTEGER UNSIGNED NOT NULL DEFAULT 0,
EmployeeID INTEGER UNSIGNED NOT NULL DEFAULT 0,
Bonus DECIMAL,
PRIMARY KEY(ContractID, EmployeeID)
)

Then, you can

SELECT ContractID, EMPLOYEE1, EMPLOYEE1_BONUS
INTO EmpBonus
FROM CONTRACTS WHERE NOT EMPLOYEE1 IS NULL;

Then repeat for EMPLOYEE[2-4]

Then you can do the queries that you want to, like

SELECT Bonus from EmpBonus WHERE EmployeeID='xxxxx';

OF course, if there is other contract information (i.e. client, address, etc), you would have another table with ContractID as the primary key and you would store that data there.