Enforce one-to-one relationship?

[Imdabaum]

I have an application where I create a record anytime someone submits a request form. I enter the data from the request form and the application calculates what the salesman should be paid.

If this salesman has a manager then the application automatically creates a new record for the manager.


TABLE1
ID
SalesmanID
INVOICE1
INVOICE2
CUSTID
Plan_Type_CD
Monthly_Payment
ManagerID
EarnedAs
MGR_RecID
Notes

Currently I am relating the MGR_RecID to the Table1 ID field. I have seen this done, but I don't know if it's the best practice. ie having a field in a table related to a field in the same table.

But the join interprets this as the MGR_RecID having a one-to-many relationship with the other records, when in fact For each ID there can only be at most 1 MGR_RecID created.

So if I create the first record (ID=1) the manager's record is submitted as well (ID=2-or next availble ID). In the record where ID=1, the MGR_RecID =2.

The inserts work without even having the MGR_RecID field. But deleting is another story because without the relationship, there's no way to delete both records without adding more code. Essentially, I want to cascade delete to the Manager's record if the employee's record is deleted.

[austenr]

From the Microsoft site:

http://office.microsoft.com/en-us/access/ha011739511033.aspx

Cascade delete

[OBP]

Why not break out the managerId and Manager details in to a new Table with a 1 to1 Relationship?

[Imdabaum]

I understand cascade delete. But in order for it to work, I need to have the relationship set up, and I don't think I've ever made a 1 to 1 relationship before.

If I understand the threads that I've read on 1-1 relationships,

TABLE1
ID 'PK
SalesmanID
INVOICE1
INVOICE2
CUSTID
Plan_Type_CD
Monthly_Payment
ManagerID
EarnedAs
MGR_RecID
Notes

TABLE2
ID 'PK
MGR_ID
INVOICE1
INVOICE2
CUSTID
Plan_Type_CD
Monthly_Payment
Notes

And link both IDs in the table and make both of them primary keys? This doesn't mean that every record in table1 needs a record in table2, but every record in table1 can have at most one record in table2. Correct?

[austenr]

It would apear that you have issues with your table normalization.
You have invoices in both tables, thus you are storing redundant data.

[Imdabaum]

It would apear that you have issues with your table normalization.
You have invoices in both tables, thus you are storing redundant data.

I haven't created that table2 because it seems awefully redundant and beyond the means to break normalization rules. I was just trying to clarify what the 1-1 relationship would entail.

Table2
ID 'links to Table1 ID
MGR_RecID 'would be the corresponding Table1 ID that was created by the employee. Yeah?

SAMPLE DATA:
TABLE1
Employee Record
ID=2 data ........
Manager Record
ID=3 data.........

TABLE2
ID =1
EMP_REC= 2
Mgr_RECID = 3?

SAMPLE TABLES:
Table1
TABLE1
ID 'PK
SalesmanID
INVOICE1
INVOICE2
CUSTID
Plan_Type_CD
Monthly_Payment
ManagerID 'Might be able to delete this field
EarnedAs
Notes

Table2
ID 'PK
MGR_RECID which would be the record number of the corresponding record in TABLE1?

I realise I sound like I don't know what I'm doing, but that's because I don't when it comes to 1-1 relationships.
MsgBox("Bonus points for honesty?", vbyesno)