Novice frustrated with keys! This seems it should be fundamental, but...
From my understaning of table relationships, it seems to me that my problem should have a very basic, built-in solution, but I can't for the life of me figure it out. Here's a simple senario to illustrate my problem.
In real space, addresses are associated with houses and names are associated with people. To me it would make sense, then, to have a "Houses" table and a "People" table. For simpllicity, each table has two fields. The houses table has an "Address" field and a "pk_House_Id" field. The people table has a "fk_House_Id" field and a "Name" field. The "*k_House_Id" fields are the Match fields for a one-house-to-many-people relationship. If there are two records in the "Houses" table with addresses A and B and one record in the "People" table with the name "Jim" and Jim currently lives in (and therefore holds the key to) house A, how does FMP move Jim to house B? Take the House-A key away and give him the house-B key, right? From a user's perspective, Match Keys are meaningless, so what is the best way to do this without confronting the user with manipuating, or even seeing key fields?
Dis/re-associating records by simple key manipulation seems like such a fundamental advantage of relational db's but I can't for the life of me figure out how to do this. I'd be grateful for any pointers.