One to One Relationships
For some reason I'm a little confused on one to one relationships and what best practice would be.
Let's say there are two tables, A and B, and that we want them to be related to each other on a one to one basis. Lets also assume that these could actually be one merged table, but that we would prefer to keep them separate (because if they were merged we would have a preponderance of empty cells, because just because a record in A exists, doesn't mean an associated record will exit in B, and vice versa).
So we could use a script, two actually, depending on our context. If we are in table A and we trigger the script, it would capture the Unique ID for that record in A, and then move to table B, and create a record, and store that captured copy of the Unique ID in a foreign field that relates back to table A, then we could capture the Unique Record for the newly created record in table B, and move back to table A and our initial record, then store this second capture in a foreign field that relates over to table A.
For the context of table B, we would create a script the same as above, but where A is replaced with B (or the two are switch, the old vice versa routine).
Our foreign fields, in both table A and table B, would be set to Unique under the validation tab of manage database.
So is this the proper and best way? Or do I have my head screwed on backwards? Its just that I haven't seen anything in print that ends up with schema that looks like this (not in books anyway).