1-1 relationship

[lpapad]

When do you find it suitable to create 1-1 relationships ?

 

[Mark_]

? When it is required ?

Your question is overly broad. You may wish to give a more clear description of what you are asking to illuminate what type of answer you are looking for.

 

[lpapad]

I understand 1-1 relationship as a table split, and I do not find any use for it. Does anyone has any experience in implementing it in a case study?

 

[Mark_]

Application 1 has unique records.

Application 2 needs to access a table in Application 1 but cannot change the table structures.

Application 2 may have its own table that is linked, 1 to 1, to Application 1.

Think about it for a moment. If you have a POS system that lacks customer tracking, you may have a "Customer" file in the POS system that lacks a LOT of details you need. You DO need to see your current customers though, but if you can't change the POS system, you may set up a 1 to 1 relationship between your customer tracking and the "Customer" table in the POS.

Many 1 to 1 relationship evolve from these kinds of situations. I wouldn't call them a "Case study", more of a "Were stuck doing it this way".

 

[The_Doc_Man]

When I was a sys admin for the U.S. Navy Reserve's personnel management system, we had over 240 tables. These tables included the base PERS table and many 1-1 tables that were details relevant only to certain applications and that were kept separate because of Privacy Act or HIPAA requirements. We would JOIN the PERS table to any one of several other tables depending on the exact report we wanted. It was a toss-up as to whether a particular relationship was 1-1 or 1-many. But in our case, the 1-1 tables were that way for privacy/security isolation. When that happened, the security on the secondary tables often was more restrictive than on the base table.

 

[Mark_]

@ Doc,

I'm dealing with a situation like that now. One system for tracking health records. Second for tracking students. I'm working to replace the health tracking system but work keeps getting in the way.

 

[CJ_London]

I also have a current situation

a table of transactions which need to be reconciled - the reconciliation flag is maintained in a separate 1-1 table. Why? because to reconcile you need to use group by queries - which cannot then form part of an update query. No record in the reconciliation table means not reconciled.

And when you are talking 500k+ records updates take longer than inserts.

 

[Mark_]

@ Pat,

Politics and programming do not mix.

For your case 2, couldn't that actually be thought of as a custom "One to many"? Could an employee also be a customer in that system or would they need two records (in theory)?

 

[jleach]

I've used it in a number of scenarios. Inventory tracking was one. We have a number of different inventory types for a manufacturing facility (purchased goods, manufactured goods, consumables), each with their own unique IDs across all inventory types.

One master inventory table which lists all the entries and their types, along with common properties, and "extension" table to list the properties specific to each type of inventory.

Document tracking is another place I've used them.