1-1 relationship (1 Viewer)

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

? 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.

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?

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".

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.

@ 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.

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.

I've seen two uses.
1. Security as already mentioned. Some RDBMS' allow you to secure individual columns but others do not. If your RDBMS does not implement colum level security, you can use 1-1 relationships to add higher levels of security.
2. When you have an entity that can take multiple forms and you need to keep specialized data for each entity type. You might find this in an application that includes Employees, Customers, and Vendors. In some places in the application, the entities are interchangeable and so you want to be able to have one table to join to but since each type has special attributes, you create an entity table with a type field and all the common attributes and three one-side tables with the specific attributes for each type. For relationships to contacts and addresses, ALL relationships go to tblEntity rather than to the specific type tables.

The 1-1 is extremely rare and frequently mis-used by people who have their excel hat on and think that repeating groups like year, month, day, etc should be columns rather than rows and so they often run out of room with Access' limitation of 255 columns. Or sometimes they just think they are being organized by grouping attributes into multiple tables. There is a horrendous example of this mistake in production in my state. A vendor who shall remain nameless created a survey application and made each question a separate column in a table. So rather than a standard survey where questions are rows and you have junction tables to put them into groups for reporting and display, they separated the questions into 100 tables. Talk about job security. Want to add a question, pay for them to modify the table and any associated forms and queries. They actually hard-coded the questions onto web pages!!!! They also charged Three million + and took almost three times as long as they estimated to build the app which turned out to be over two years. Plus because they saw this as a real time requirement, the user companies had to purchase iPads and hotspots for every care manager who worked in the field. My bid to do the project using Access with offline collection using their existing laptops without requiring them to bring their own hotspots was so low by comparison that the PTB thought it was a joke. I guess the joke was on me since instead of getting paid millions of dollars for a project that should have cost under $100,000, I got to pay for the boondoggle with my tax money.

I still don't know whether the application designers were just incompetent or simply decided to make the most inefficient design possible in order to extract the largest possible fee along with a long term maintenance contract.

@ 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)?

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.