One-to-one table relationships? Curse or Cure?

[Rx_]

Was just in a philosophical argument with another DBA about the wisdom of using one-to-one tables. He had some good arguments against, so my counter argument involved a shipping crate, transport jet, and the island of Haiti.

However, to prove that I have an open mind, the question comes up about the use of One-to-One tables.

MS Access - linked tables to SQL Server:
The Primary table (a.k.a. Customers type table in Northwind) has about 45 fields.
The Rules Engine Meta data table has about 11 fields. Most fields are boolean or integer.

Every-time a new record is created in the Primary table, a record with the primary ID is created in the Rule Engine table.

Often, these are not really linked together. They serve two different purposes.

On the other hand, it could all be in one bigger table.
Going from 45 to 56 isn't that much of an impact on our dedicated SQL Server.

Security is one reason to split a one-to-one table. If users can get in and modify one table, and not have access to another.
A Read-Only table (e.g. Linked Server Table) is another reason to have a one-to-one table.

If any one has any pro's or con's, please share.
My thought is to prototype as a one-to-one table. Then as the design becomes solid, move the 11 fields into the Primary table.

 

[Galaxiom]

Security is not an especially compelling reason for it. SQL Server has fine-grained security. Users can be assigned read and write priviliges on individual fields. Another alternative is Views configured for different users groups.

Without knowing more about your Rules table it is hard to comment but I wonder if thta table aught to be a different structure with less fields and more records.

 

[gemma-the-husky]

I always thought there were special reasons for one to ones, such as security, and managing large data sets, with too many fields, and perhaps also handling data that was sometimes sparse, for certain record types within a given table.

Galaxiom indicates security is not an issue in SQL, although will be with access/jet/ace tables.

other than that, I find it hard to identify a reason for one to one tables

 

[Galaxiom]

managing large data sets, with too many fields,

Generaly too many fields suggests a data structure problem. Even coming close to the 256 (theoretically, though practically somewhat less) limit on Access is likely to be an issue of this nature.

If you get near the 1028 columns (or 30,000 in a wide table ) in SQL Server it is definitely time to panic.

Of course there are also record width limits (about 4K for Access and 8K for SQL Server) that can come in before the maximum number of fields is reached.

and perhaps also handling data that was sometimes sparse, for certain record types within a given table.

In Access this does not really apply because blanks are not stored. It does make sense in SQL Server for the fixed width types.

 

[Rx_]

Thanks everyone for the excellent responses. Ran this up the flag-pole with some other users at the Denver Access Users Group.
- Nobody likes linked one-to-one tables
- They should be taken out behind the woodshed (it is an old western phrase).

I started one to prototype as the rule engine is new territory and unique.
Started with 11 fields, but the design is bringing that down to four.
At least it allowed me to go into design mode on a table in the live DB without affecting users.
An example of a rule-engine field : for a historical Customer, before the database kept track of many things, we don't care if the rules are run for the customer status. So, a Field is named IsHistorical (T/F) If True - then don't bother running the rules on this record.

My plan is to move thiese four field with the data into the Primary table.

Just as a side-note, it seems that every training class uses a one-to-one table for some academic drill. Guess it makes the instructor's troubleshooting job easier for a query join?

 

[ButtonMoon]

What most people call "one to one" very often means "one to zero or one". In other words one side of the relationship is optional. This is sometimes called Z-cardinality and is different from strict "one-to-one". Strict one-to-one relationships between two tables in most cases tend to be impossible or very difficult to enforce because inserts/updates/delete require both tables to be updated simultaneously and most DBMSs in use today do not support atomic multi-table operations.

Z-cardinality on the other hand is perhaps more common and more useful. For example an employee may or may not be enrolled in a pension scheme and the relationship between an employee and his pension enrollement is therefore optional. Z-cardinality is a natural consequence of applying Boyce-Codd Normal Form to such a situation. An alternative would be to bundle all the pension adttributes into the employee table, which would mean populating it with nulls or dummy values and would very likely imply some serious compromises with data integrity.

 

[gemma-the-husky]

What most people call "one to one" very often means "one to zero or one". In other words one side of the relationship is optional. This is sometimes called Z-cardinality and is different from strict "one-to-one". Strict one-to-one relationships between two tables in most cases tend to be impossible or very difficult to enforce because inserts/updates/delete require both tables to be updated simultaneously and most DBMSs in use today do not support atomic multi-table operations.

Z-cardinality on the other hand is perhaps more common and more useful. For example an employee may or may not be enrolled in a pension scheme and the relationship between an employee and his pension enrollement is therefore optional. Z-cardinality is a natural consequence of applying Boyce-Codd Normal Form to such a situation. An alternative would be to bundle all the pension adttributes into the employee table, which would mean populating it with nulls or dummy values and would very likely imply some serious compromises with data integrity.

nicely put. that's the sort of thing I meant, when I was talking about sparse data earlier.

In practice, you can just have a flag to indicate whether the optional fields apply, and just use one table - avoiding the need to manage the link.

Depends whether you want to try and auto-manage the extra table.

 

[Old Man Devin]

I remember reading in an old Access manual that a recommended use of one-to-one tables was for things that are going to be temporary. The example they gave was something like having a ping-pong tournament details table that was one-to-one with your company's staff, since their ping-pong results probably won't be needed in the main table forever, but you might want them in the database to analyse in some way. And again I suspect this example also cover the Z-cardinality issue mentioned about (unless the company feels like having a mandatory ping-pong tournament for all employees... ).

 

[wader]

I'm not so sure I understand why one-to-one is so bad (i.e. taken out back). I use it often when a record must have one and only one attribute for the business requirement. For example where the business requirement is strict that a project may only have one approver. I.e. the approver's table is set to one to one on the project main table. I certanily understand the benefits of one to many but why so one-to-one averse?

 

[Frothingslosh]

I'm not so sure I understand why one-to-one is so bad (i.e. taken out back). I use it often when a record must have one and only one attribute for the business requirement. For example where the business requirement is strict that a project may only have one approver. I.e. the approver's table is set to one to one on the project main table. I certanily understand the benefits of one to many but why so one-to-one averse?

In my experience, in a situation like this, it would be better to have an 'Approver' field, and either fill it in with the name of the approver once approval is recieved, or link it to a list of authorized approvers. (If you're linking via a numeric ID field, just treat zero as 'unapproved'. If you're linking to a string field, just use empty sets when the project is not approved.)

A one-to-one relationship between Projects and Approvers not only means that each project can have only one approver, but that each approver can approve only one project. The relationship goes both ways.

 

[wader]

A one-to-one relationship between Projects and Approvers not only means that each project can have only one approver, but that each approver can approve only one project. The relationship goes both ways.

The approvers name field in the Approvers table is populated based on a SELECT DISTINCT query from an imported HR table so that anyone that's a director or above can be assigned to a project so no conserns there. I.e. the one to one has more to do with managing the foreign key

With greater context, the approvers table houses information about the approver that is populated on project documentation produced from the db. It therefore made sense to seperate that from the Project main table. Not to get to bogged down in defending my solution but ultimately, I just wanted to make the point that one-to-one is a strong tool and can be used well.

 

[Galaxiom]

The example they gave was something like having a ping-pong tournament details table that was one-to-one with your company's staff, since their ping-pong results probably won't be needed in the main table forever, but you might want them in the database to analyse in some way.

Unless they we only going to play one match I would expect the results table to be on the many side of a one-to-many relationship.

 

[GregoryWest]

The only time I have seen a 1 to 1 table being a good idea was where one table was being updated and searched and looked at a lot, where as the other table was magnitudes larger and seldom updated or read. And example of this might be a 1 to 1 table database where the primary table contains all the text information about a video recording, and the subordinate table contains the video. You might want to read the descriptions of the videos, even update counts like number of times watched. But you would not need to load the video each and every time, until the user said "Yea like to see that one."

 

[The_Doc_Man]

For what it is worth, "behind the woodshed" isn't limited to western USA. The "Old South" fully understands woodsheds.

The ONLY time I've ever seen where a one-one relationship was absolutely required was when the boss said so. However, it does SOMETIMES make sense when you have distinct high-security segments vs. general information segments, or when one of the participants in the one/one case involves a monster memo or BLOB field that you don't want to drag around with the main record.

 

[gemma-the-husky]

Ok

A one-to-one may make sense in an employee table, say.

put generally available info in the main table
put the salary in the one-to-one table

users who cannot see the one-to-one table, can't obtain the salary.

 

[ButtonMoon]

Ok

A one-to-one may make sense in an employee table, say.

put generally available info in the main table
put the salary in the one-to-one table

users who cannot see the one-to-one table, can't obtain the salary.

Hi Dave,

I've heard this security-based suggestion before but I don't get it. As far as I know, in every DBMS I can think of security can be granted or denied at column level and not just table level. In Jet/ACE there is no role or object-level security at all. Does there really exist a DBMS where to secure a column from unauthorised eyes you have to put it in its own table?