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

 

[lespaul]

I can't verify or prove this, just my gut instinct.

I wouldn't chain the information. I would use more of a Ring model. Have the Personnel in the middle of the ring and the others tie directly to Personnel. If each of the child tables contains the Personnel ID then you would be able to join into each of them through that ID. What if you needed just the SecurityClearance information, you would have to join into Company Peronnel Private first.

I can't see anyway in heaven why someone would use this method

I have to agree with you on this one!!

So, if it was me, I would keep Personnel the main table and then include a FK from Personnel to each of the Sub tables.




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[TheAceMan1]

lespaul . . . . .

Thanks for the reply!

Yeah . . . . eveytime I run across it (if its in my hands), [blue]I always convert it proper[/blue] (as I know it). But before I do, [purple]it always appears to be working fine!.[/purple]

For quite a few years now, I've always wanted to know what the . . . so & so . . . is going on when its done this way.

[blue]Again thanks![/blue]

See Ya! . . . . . .

 

[willir]

Howdy Aceman

I've receive a proposal from a small company to design a DB

Tough issue where one is using "work-arounds" because of the security problems with Access -- ther operative word here is small

Splitting the tables is yucky, but it will allow you to use the OS to do the security for you.

One of the "gotcha's" here will be that Access not enforce referential integrity across a split database. For example, Personnel <-> Payroll. You can link the relationships but not enforce integrity.

So...
- For your primary backend, define the required relationships and enforce intergirty -- you know the drill. Perhaps you can keep as much insecure info as possible on the primary front end. For example, keep the keys, but not the meaningful data.
- Create the relations with the secondary backends. Although these are 1:1, Access will not be able to enforce this relationship.
- Use code to enforce the relationships.
- (You know this too) For performance, stay away from the use of domain agregate functions (DLookup stuff), keep the databases close to root of a mapped drive, and keep the names of the DB's short.

You may want to recommend that they outsource payroll to a bank or third party. Payroll is pretty "sensitive" stuff, and if you are actually printing cheuqes and stuff, you may have to implment tax laws ect. HUGE Liability if things get messed up, even it is not your fault.

Good luck -- but luck has nothing to do with it, you know what you are doing, and helped a zillion others with their work.

Richard

 

[TheAceMan1]

willir . . . . . .

Thanks for the the reply. All points you mentioned [blue]qualify my plan of attack.[/blue]

[purple]You forgot one thing though! . . . . My Question![/purple]

See Ya! . . . . . .

 

[Crowley16]

personally, I can't see many pros from the chaining method...

if all your tables have a 1-1 relationship with a main table, then if you just use the main table's PK then you can still navigate through all your sub-tables, but you can often skip parts of the chain...

plus if you're using a generated numeric key, then the performance will pretty much be exactly the same. Unless for somereason, the key you're using in the primary table HAS to be a huge string, I can't see many performance benefits of chaining either.

the only thing that I'd see would be advantageous over chaining is you're FORCED to implement the full chain in order to be able to navigate everywhere. But I can't think of any plausable reason why this would be needed.

--------------------
Procrastinate Now!

 

[jsteph]

It looks like the purpose of this is security/privacy? Why not implement security and use sort of a 'view' idea with owner access queries?

Since you probably will need to do full JET security anyway given the nature of the data, this would only slightly complicate your security but greatly ease the development.
--Jim

 

[TheAceMan1]

Crowley16 . . . . .

Thanks for the reply. I suspect the same as you, just looking for something concrete on the question (without have to do alot of testing).

All manner of information I've found, speaks of splitting a single table into two. I already know this. Is this chaining I've seen enough of that has me bugged. With current knowledge I'd never use it. Just need a white paper or something thats said sometning about.

Again thanks . . .

See Ya! . . . . . .

 

[TheAceMan1]

How are ya jsteph . . . . .

Thanks for the reply.

What to do is not my problem. I just want to know about this chaining thing (which doesn't make sense) . . .

See Ya! . . . . . .

 

[jsteph]

Aceman,
Ok, for pro's of the chained system...maybe only that it's easier to set up the tables, period. After that, as I see it, it's all downhill.

The Con's are mainly application complexity, and performance, since any report that needs data from all 4 parts of the table will need to hit 4 tables, which will be a minimum of 4 disk i/o's. I know with Access we generally don't worry about such low-level hardware issues with regard to performance, but it's still a factor.

So basically it's going to be an uphill climb designing forms and reports with this setup. Setting up ref. integrity is something I've never tried with a one-to-one using more than 2 tables so I don't know how (or if) that would work.

I went down that road for a brief period (about a day) on one project, and ended up using the owner-access queries--which give field-level security--and it worked out fine.

So if you're looking for a way to discourage those who are suggesting this, then I guess telling them each new report they request will take twice as much time to develop would be a good tack to take.
--Jim

 

[Crowley16]

I've never come across anything that discuss this chaining method...

probably because almost all developers automatically realise it's such a bad idea they take it for granted people wouldn't do it...

just out of interest, what is the technical level/background of the people who are suggesting you do this?

--------------------
Procrastinate Now!

 

[TheAceMan1]

[blue]just out of interest, what is the technical level/background of the people who are suggesting you do this?[/blue]

No one has ever suggested this. The question comes from my correcting it enough times in the past for jobs that came my way. Either the programmers left the company or the company was trying to get a DB together.

As for [blue]technical level/background[/blue] . . . if there chaining in this way, I see no need to ask! ;-)

There is one thing I've notice though. Most of these DBs that came with this chaining have large table counts. I'd say they average at least 75. I'll also find additional system tables such as [purple]MSysObjects_1[/purple] . . . . .

See Ya! . . . . . .

 

[Crowley16]

well, access can handle 255 field tables, so unless it goes over that (after normalisation), I wouldn't bother to split...

--------------------
Procrastinate Now!

 

[willir]

Aceman

Sorry about getting off-track in my earlier post.

I am smack in the middle of a mega project. When I missed the mark, I knew it was time to focus elsewhere for a time. Huge amount of work with a zillion mixed up deadlines and priorities.

...up for air.

"Chaining relationships"
Personnel 1 <-> 1Company Personnel Private Info 1 <-> 1 Security Clearance

I personally can see only one advantage for chaining a database as you have depicted...

Security.
In order to get to the end point, you have to pass all the previous security requriements.

This also could have been a lazy approach. To get from A -> B -> C you need to meet the requirements for B, so why not just write the code / setup the requirements once.


From my perspective, there are a few reasons why not to use this approach...
More points of failure -- Broken link. If one of the links is down, inspite of having the proper authorization, the end user will not be able to reach the data at the end (of the rainbow ;-) ) Note that the "broken link" could refer to a coding problem or setup issue, or the table is stored on an external linked table, other system issues.

Performance. If a person only needs the payroll table, they need to walk the chain, loading each database / table to get to the end. Provided they have the proper security, the more effecient way would be to just to load the payroll table / database.

Although not done, I suspect coding may be a tad more awkward. You would have to load and verify each table / database as you "walked the chain". For example, the code loads the data into one form/subform. The end user then has to click another option to load the next level, etc. Instead, a modular approach would perhaps be a little more simple where they click once or only see the options they have security for.

A little less flexibility. The end user has to "walk the entire chain" to get to the payroll info. This means that anybody that can access payroll, also has access to everything else. From my perspective, it is possible that somebody with security access may not need payroll access. And somebody with payroll access may not need security access.

Twang...
Closer to the mark?

Richard

 

[TheAceMan1]

Haaay Willir . . . . .

Of course I agree with you in all ways!

The epitome of all this is [blue]backup[/blue] in explaining to people why they shouldn't do this. In the past its always taken some time to convince those involved of the err is using this schema. You now how it is . . . . [blue]a reference[/blue] . . . [blue]or a white paper[/blue] would save alot of convincing. Just never could find anything on it!

[purple]Thanks Ol Buddy . . . . you take care . . . . Ya Hear![/purple]

See Ya! . . . . . .

 

[willir]

You now how it is . . . . a reference . . . or a white paper would save alot of convincing

Second normal form...
...all non-key columns must be fully dependent on the primary key...

In a sense, breaking a table into different tables to resolve a security issue, etc breaks the 2nd normal form rule for normalization. Data for the "primary key" are being stored outside the table.

One might justify breaking the rule, and accept the consequences. Consequences here would be more coding and administration, possible data problems if not done right.

Daisy-chaining the tables together really violates the rule, in my opinion, and the consequences can be more extreme. Same consequences but much higher risk.

Not sure if you would find references to this type of thing. Although others may have used this approach, I have never seen this design before. It would have to have enough visibility or academic interest before the gerus tore it apart. Some might just dismiss the issue and move on without pursuing it.

Zooming out.

 

[BobRodes]

Well, there are enormous numbers of papers on relational database theory. The reason you can't find anything on why people shouldn't do this is pretty much the same reason that you can't find any recipes for egg nog that say "avoid using ostrich eggs."

So, rather than looking for some authority that explains why your customer is wrong, just get material to show how the idea violates basic rules of database theory. So, to take your quote:

What are the pros, cons and operational impact, or whatever, of chained one to one relationships!

pros: allows you to use dBase II storage format with more than 65,536 records. Of course, dBase II was old 20 years ago.
cons: violates basic rules of database theory, as have evolved over the past 50 years. Would create serious performance issues, as well as serious code overhead, both of which have been well documented here.
operational impact: see "cons" above.
whatever: please clarify.

The reason there aren't any white papers on this is that the idea is too trivial a violation of database theory to be worth taking up in detail. If you need to prove your point, then prepare and deliver a lecture on the normal forms, especially the first two, and show where this is a violation thereof.

For the record, lespaul's "gut instinct" is absolutely correct in my experience. On the other hand, I would ask your customers to justify their reasons for pulling the data into four tables at all, and furthermore find out more about their "need for speed". The point is, if the specs don't conform to the needs, that's your fault if you're the developer, even if you do exactly what the customer asks for.

Cautionary tale: I remember a lawyer who gave me a set of written specs. I spent a few hours with the people in his office, in which time it became clear he had no idea of how his office really ran, and also that his specs were entirely non-responsive to his needs. Of course, he really needed more time than he had budgeted as well. So, I rewrote the specs and gave them to him, at which point he politely threw me out of his office, and then attempted to tell my boss that he wouldn't pay for time already accrued because I had spent all that time hitting on the women in his office!! (They settled for 50 cents on the dollar, I believe, probably he had that figure in mind in the first place.) That said, it would have been a whole lot worse if I had attempted to do what he asked. When he found out that it didn't work, it would be my fault, not his.

Bob

 

[TheAceMan1]

To All . . . . .

Wile working up a relationship model, I ran into the basic reason why some people do this chaining, and I hate it when simple basics finds it way to elude me at times (or I elude basics).

[blue]You can't have more than 32 indexes on a table.[/blue]

. . . and microsoft suggests you leave a couple for use when compacting (per table)! Now were down to 30.

[purple]So what do you do when you know your gonna go over the limit?[/purple]

In the past I've always been able to recombine tables that really don't need splitting to get inside the limit. [blue]But not this time![/blue] Present DB has over 100 tables of which 77 require relationships (14 [blue]1 to 1[/blue], the rest [blue]many to many[/blue]).

Note: Management has fully explained what they want from the DB, and I have to agree, they know what their doing and what they want. I can give a good arguement about maybe 8 tables, but it would be ambiguous, as required indexes are still way over 30.

Forced to violate the [blue]normalization rule[/blue] of redundant data (only on the PK!), I decided to use [blue]3 extension tables[/blue] to circumvent the relationship problem. Schema as follows:

[blue][purple]tblPrimary[/purple]
PrimID PK as Long
OtherFields

[purple]tblExt1[/purple]
PrimID PK as Long 1 to 1, Child to tblPrimary.PrimID
[green]NoOtherFields[/green]

[purple]tblExt2[/purple]
PrimID PK as Long 1 to 1, Child to tblPrimary.PrimID
[green]NoOtherFields[/green]

[purple]tblExt2[/purple]
PrimID PK as Long 1 to 1, Child to tblPrimary.PrimID
[green]NoOtherFields[/green]
[/blue]

Now I have [purple]90 indexes[/purple] I can use. A small price to pay, but I'd rather have the [blue]Referential Integrity[/blue] than not!

[blue]Second normal form...
...all [purple]non-key columns[/purple] must be fully dependent on the primary key...

In a sense, [purple]breaking a table into different tables to resolve a security issue, etc breaks the 2nd normal[/purple] . . .[/blue]

I disagree . . .
[ol][li]A proper [blue]1 to 1[/blue] relationship is established between the [blue]same PK of two tables[/blue]. Each non-key field is dependent on the PK of their respective table. Just in this case, [blue]only one record per PK allowed.[/blue][/li]
[li]A [blue]1 to 1[/blue] split for security reasons, easily solves a big issue here. Example:
Most companies with networks have an [blue]Administration Group[/blue] and a [blue]PayRoll Group[/blue]. Initially, admin data & payroll data are both in the same table. Both groups need to see the admin data, but [purple]how do you keep the Administration Group from seeing the payroll data?[/purple] It can be done in code, but not only can this turn out to be a wieldly experience . . . [blue]code can be broken![/blue] Besides in access group level security, you [purple]can't protect a field[/purple]. However its [blue]easy[/blue] to set protection for a table! With a split table, those hours of programming are saved as well. Its worked great for me. When I'm setting security,I love running across those tables and . . . . . blamo! . . . . . you won't get to see this table![/li]
[li]The only other reason I've come across to split a table is if field count will exceed 255.[/li][/ol]

[purple]We'll . . . I'm back at the drawing board . . . everyone take care![/purple] ;-)

See Ya! . . . . . .

 

[willir]

Good research AceMan, and great use of brain matter.

Perhaps we can have a (cyber) beer to discuss further. Clink. I think in my original post I gave a similar justification to why one can be justified in breaking up a table to address security issues.

Sounds like a real interesting project.

 

[jsteph]

how do you keep the Administration Group from seeing the payroll data?

As I've said before, an OWNER ACCESS query does this neatly.

When JET security is set up properly (which, in my opinion is a better route than this chaining thing), your table in question can have NO RIGHTS WHATSOEVER to ANY user.

All forms, etc, are based on queries owned by the 'superadmin' (ie, not 'Admin'), but set up as OWNER ACCESS.

Then query A for normal people has fields they can see.

Query B for Payroll has fields only they can see, and so on.

The query itself can be set with security to not allow updates, for instance, so even though a user can see fields via the owner-access mechanism, they can't update them, even though the owner can.
--Jim