one to one relationship

[BittenApple]

Hello team,
I have two tables: the left table pk is a foreign key on the left table, but I wanted these two to have one to one relationship.
(The reason for it was that the requirement was a combo box to populate a form)

To create one on one relationship between these two tables, I set the index on foreign key to yes no duplicates. It worked perfect for many days.

I split the database and an other end user and I started testing the front end.

That end user started getting an error message especially when we wanted to do edition on same record:

The error message was:

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

I removed index as unique (that is only option which I had), removed referential integrity (Since once I have it the relationship changes to one to many) and I only set the foreign key as a required key. I and another end user started putting data entry on the form, it went well and everything seemed well and I check the tables, they edition and adds were correct.

Does what I have done is correct? What is your suggestion? The PK on the left table was hos_ID and hos_ID was a foreign key on the right table.

Please noted that we have 100 hos_ID in a combo box that when we select, it populates the form. That was the requirement.

I appreciate your help,
apple.

[HansV]

There is very rarely a good reason for creating a one-to-one relationship between tables.

What are the two tables for?

[BittenApple]

The first table is:
HospitalTable
hos_ID: AutoNumber
Hos_Name: Text

The second table is:
Transaction table
hos_ID Number Foreign key

The first table feeds a combo box and user selects a hospital Name from it.
The second table has many text boxes and end user selects one entry (one hospital) from the combo box and then on the form (transaction table) , he/ she enters some texts in text boxes related to that hospital. This was the requirement. I have 100 hospitals, they want to see the last revision on the textboxes. That is why this has been designed like this.
Regards,
Appapp

[HansV]

Since one hospital can have several transactions, the relationship from the hospital table to the transaction table is one-to-many, not one-to-one. If it were one-to-one, there could be only one transaction per hospital (in which case you wouldn't need the transaction table - simply add the fields to the hospital table).

[BittenApple]

Hello Hans,
yes, that is correct, I have 100 hospitals and I have only 100 transactionsIDs or 100 records in transaction table.
I can add the fields to hospital table, but I have to make so many changes on it. Can I keep it as it is as long as it works?
Regards,
appapp

[HansV]

It's not good design...

[BittenApple]

Hello Hans,
Yes, I agree this is not a good design. I could have created all of these out one table. Create a look up field probably.

I think the message I got is a correct message and it makes sense. It says, it create duplicate records. In fact it is correct. For each hospital, I should only have one record and the relationship is one on one. I should get that message.

I changed the record lock from no lock to edit record, so the other user can only view the record and he/ she won't be able to edit it.

You said that there is rarely a very good reason to create one on one.
I wonder if we have once credit card per customer or one car company per employee, then we have to create one on one. What is your thought on this?

Thanks for all you do!!!
appapp

[HansV]

If a customer has only one credit card, you should not create a separate table for credit card numbers, but create a credit card number field in the customer table.

If an employee has only one car, you should not create a separate table for car license numbers etc., but create a car license number field in the employee table.

[BittenApple]

Hans,

I absolutely agree with you. On the way, I was asked to do one on one and I made some search on line and learned how. I didn't think of the way you are showing me. It is hard to go back and change everything (actually I did but I noticed that I have to start from scratch); so I stay with whatever I have built. Hope it goes well.

Regards,
appapp