One to one - doesn't anyone else have this problem? 2

[paron]

I am working on the Human Resources portion of my agencies' db. There is employee data that is "public in agency," like: Office Phone Extension, Workgroup, etc. There is also data that is no-one's business outside Human Resources, like: Date of Birth or Annual Compensation.

Accordingly, I am splitting the data into two tables in a one-to-one relationship so that I can apply appropriate permissions to each table. tEmployees has all the personal information, and tEmployeesWork has all the "public in agency" stuff.

Keeping the EmployeeID synchronized in both tables I guess I'll handle by means of the forms I provide the user. I might use a non-visible textbox bound to tEmployees.EmployeeID on the form, with its Default Value set to "= tEmployeesWork.EmployeeID." Or, I might use the Form/Subform technique described in thread700-229595. Both techniques seem kludgey.

My questions, then:

First, is this structure that unusual? All the Web research I've done says that "one-to-one" is somewhere between "not often used" and "counter to the principles of relational db design." How do other people handle these situations?

Second, isn't there a more graceful way to keep the tables in synch?

Ron

 

[WildHare]

When I've been faced with this situation, I've tightly controlled the user's view of the data - in other words, specific forms and queries for specific people. I NEVER let users get at raw tables, and I NEVER let them sit at the standard Access database window. I've been lucky, I guess, in that I haven't had to protect against semi-talented "curious" users who know how to get around Access admittely swiss-cheesey security features.

I've done several apps with sensitive information in the records, and have always had success with the above techniques.

Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[LittleSmudge]

I've used both approaches.

For the split table approach I even had the two tables in two separate databases on two separate computers on the network ( paranoid HR manager !! )

Other DataBase Analysts that I was working with at the time were also quite comfortable with the technique.


I've also used the technique when an existing database was available to read and write to (but not edit structure because it was 'ownered' by a 'third party')
However, a new application needed to extend the fields that were recorded. A simple One-to-One relationship fixed the problem.

Not common - I'll accept - & intuitively counter to the Relational Database concept. But on the other hand not exactly 'rocking horse droppings' either


G LS

 

[paron]

Thank you both -- I think that I'll leave it as a two-table structure, so that I can allow users who have the expertise to make use of the raw data that is in the "public in agency" table.

That approach also avoids the problem of tracking and controlling distribution of our apps -- if I control data access by which apps are available to various users, then I have to control which users get which apps. In our setup, that could be more work than maintaining the tables in a one-to-one.

Thanks again; your input helped make the decision easier. There are options, but now that you've explained them, I think the one-to-one setup is the lesser of two evils in this case.

 

[khwaja]

I started a thread on a similar subject in this forum. I had the same problem and I cme to the same conclusion. Using hidden form in the main form to update was the only only solution I could think of. So thank you for reinforcing my decision too.

Cheers