One to One Relationships Question? 2

[TheAceMan1]

Howdy All!

I've had to split tables with [blue]one to one[/blue] relationships many times in the past. However, its never been more that a [blue]single split[/blue].

I've receive a proposal from a small company to design a DB and from info provided, for security reasons, a main table needs to be split into four:

[blue]Personnel (Current Base Table)
Company Personnel Private Info
Security Clearance Info
PayRoll[/blue]

Off the top, this dictates to me a seperate [blue]one to one[/blue] between Personnel & each of the other three tables.

Far too great a many times I've seen designers using chained [blue]one to one[/blue] relationships. That is:

[blue]Personnel [purple]one to one[/purple] to Company Personnel Private Info
Company Personnel Private Info [purple]one to one[/purple] to Security Clearance Info . . . and so on.[/blue]

Although from my experience I can't see anyway in heaven why someone would use this method, [purple]I have to admit[/purple], I know neither the [blue]pros nor cons nor operational impact[/blue] of it, nor has a hardcore search for over a week now, produced any results. So the question is:

[blue]What are the [purple]pros[/purple], [purple]cons[/purple] and [purple]operational impact[/purple], or [purple]whatever[/purple], of [purple]chained one to one relationships![/purple][/blue]

The company is starting to press for an answer, and I'd like to come in contact with this info before I do ([purple]and settle this once and for all![/purple]).

See Ya! . . . . . .

 

[r937]

just want to add my comment, as this needs to be emphasized

splitting the table in the manner discussed does not violate any of the normal forms, nor does it violate "basic rules of database theory"

those who think it does, need to go back and do some more research





r937.com | rudy.ca

 

[TheAceMan1]

Haaay jsteph . . . . .

Yes! . . . can be done with [blue]OWNER ACCESS[/blue] (have to admit I've all but forgotton about it) . . . But:

Since I split my first table years ago, I've never had to think about [blue]OWNER ACCESS[/blue] since. The split table saves bothering with this method. Besides . . . [blue]OWNER ACCESS[/blue] can only be used on a secure system, and [blue]its purpose gives users in groups access they would not normally have.[/blue] [purple]This works against enforcing security.[/purple] If I had to think about using this method, I'd have to take another good look at my setup. Even Microsoft saids:

[blue]If you want to enforce workgroup security settings and users' permissions, [purple]do not include the WITH OWNERACCESS OPTION declaration.[/purple][/blue]

My experience has proven Microsoft is correct, and I'm just saying:

[blue]If your gonna use this method, [purple]give it more thought before you do![/purple][/blue]

[purple]You take care jsteph . . . Ya Hear! . . .[/purple]

See Ya! . . . . . .

 

[jsteph]

Taken literally:

If you want to enforce workgroup security settings and users' permissions, do not include...

Yes literally but logically this is actually doing what you want:
Owner Access is for creating Field-level security in Access.

Literally, you remove ALL permissions for TableA from 'Admin', so literally, using an OwnerAccess query that lets 'Admin' see *any* fields is breaking the literal security you set up.

But logically you want 'Admin' to see 'Employee ID' and 'EmployeeName'. So QueryA has these 2 fields, and 'Admin' has Read rights on that query, which is an OwnerAccess query.
QueryB is OwnerAccess and has Empname, etc, but also, say, 'PayRate'. User (or group) 'Payroll' is the only one with rights to this query. 'Admin' can't open QueryB, only QueryA.

Problem solved. Yes, there is a bit of overhead setting permissions, but in a secured db, this is already there.
--Jim

 

[jsteph]

Aceman,
That article is somewhat confusing, it goes on to say:

It is useful only in security-enabled multiuser implementations.

...not sure how they are using the term 'Workgroup security settings' in the earlier quote, I guess it has to do with having the .mdw there. If the 'God' user (the owner of all the objects) is in the .mdw, it is vulerable to hacking. I've done some things that effectively hide this .mdw but the bottom line is that a tenacious, serious, hacker could eventually get into it with the .mdw cracks out there.

However, in my opinion, if your app is in such an environment, the perhaps Access is not the correct tool if the security is that important.

I dared our top Access guy here to so much as find the .mdb let alone the .mdw in my app and he couldn't. I open the app via a short VB .exe that shells Access with a commandline, calling the renamed .mdb (with a .dll extension) using /wrkgrp to the .mdw, which was similarly renamed, and stuck in the system32 directory blending in with all the file noise there. I hack the MRU list in the registry so he can't simply look in the mru list.

If he were slick, he'd do a Search for the .ldb file (I haven't figured how to get the .ldb to be created with a .dll extension--it always takes the .mdw's name with a .ldb extension).

Anyway, I'm sure I could further hide the .ldb, but there's a balance here--how crazy do I want to get? For the truly critical apps, I have the whole app in VB with sql-server backend.
--Jim