One-to-one relationship?

[MaddiMond]

I have a main table where sales information get's entered (many to) and another table, that links to this table and lists first and last name (one).

All employees belong to a team and one report lists sales by team. Instead of indicating for every record in the sales table to which team they belong to, I put another column in the employee name table indicating which team they belong to with a number that again links to a table with the team names. I don't know if the design I used makes sense because if an employee is going to change a team or the team name changes, then I have to permanently change the number in the employee table and old records will, at least in the team report, not make sense anymore. It would require much more work to indicate for every individual sales record which team it has to be assigned to. Do I have to redesign the database?
Thanks.

Maddi

 

[gizzu]

Yes, probably you have to redesign the database. To get around the problem with changing team names, add a column named ID to the team table being of type Counter, then have the team field in the employee table of type Number, Long Integer. then each team will have a number that makes no sense except for the connection between employees and teams. This is a good general rule for designing databases: Always have an ID field that makes no sense in real world, because if it makes no sense in real world, it won't change! However, be extremely careful when you do these changes, and ALWAYS backup your database before trying to do anything, you don't want to end up losing information!

 

[MaddiMond]

I linked the employee table and the team table by a number field already, but I linked a long integer number field and the autonmunber field in the team table. The problem is that when I add a new team name and I change the number in the employee tabe to match the new team number, then the old records will be change because the employee previously belonged to a different team. My question now is, if I am breaking some important rule with my current design and if I have to assign the team number in the sales table.

Rahel