Thread: Relationshpis among tables

Hi, I'm new at Access, and I need some help.
Recently, I had to create relationship between 2 tables (to tell the truth, there were more than 2 tables). I know how to create relations, but I need confirmation that I understood it properly.

Pls see attchment

Hi,

the idea with normalised tables is that you do not enter information twice. If you collect the first name and last name of a person in the Employee table, you do not need to enter that information in the purchase order table.

the Employee table should have a unique key, set to auto increment a number, so each new record gets a new, unique code.

The Purchase order table should also have a unique key, set to auto increment a number, so each new record gets a new, unique code.

In the Purchase order table you include one field for the unique Employee ID, to identify the Employee who belongs to the purchase record. That EmployeID is then linked to the unique ID in the Employee table.

Currently, your design will not work, if you link on First name as unique ID, and then the new guy starts and is also called Joe.

In a data entry form for the Purchase Order table, you would have a listing of Employe names, to help the user select the correct name, but store only the corresponding ID in the table.

see attached on sheet 2

hth

Excelent explanation.
So I have to automatize unique numbering in EmployeeTable, then manually enter coresponding ID number in Purchase table, which should have own ID, right?

yep. My applied Access is a bit rusty, but the principle holds for any relational db

I "occasionally" break this very basic rule but only in extreme circumstances... for ex. if I want say a single table for reporting (webclient) and to improve performance I want to avoid cross joins and/or having to add INDEXes to the tables (INDEXing slows input) then I may end up with a db that stores the same info. in 1+ tables... but this is not the way to go - ie is frowned upon and for good reason.

As Teylyn states you shouldn't do the above... the key reason for avoiding this practice is data integrity... for ex. were you to change the name assigned to a given Ee ID then if the tables are structured correctly (ie other tables store only the Ee ID) then no further changes are required if however you've adopted the bad-practice of "self contained" tables - for ex. I have another table which stores both Ee ID and name (to avoid joins) then I would of course need to replicate the change made to the primary Ee table in this self contained table also and obviously this practice brings with it the risk of inconsistencies in your data should you forget to update all nec. tables

Thanks to both of u....