PK self join relationship

[pablavo]

Hi There,

I'm sure we all know of the Northwind self join, "employees" and the "reports to" field

I have a question about the same situation.

The DB I'm working on has the exact same situation, the only difference is that it's not a autonumber PK but a natural PK, one that's quite a bit longer.

There's employees and their "Line Managers" which are also employees. I was thinking about leaving it the way it was so that each record within the table had the line manager named rather than having a field with the PK self join.

The only reason for this is because the PK is quite long "General 1 Assist" (that kind of thing) and I don't see the benifit in having this in two fields within the same table.

Can anyone help me out and tell me what your opinion is....

Any help would be grateful.

Thanks

 

[neileg]

Madness, I tell you, madness!

Instead of storing a longish PK you want to store the name which is going to be longish, anyway? And when the line manger is replaced you'll have to change every employee record with this name?

The repetition of the key value, once as a PK and then as a FK is the right way of doing it.

I wouldn't use the natural key, anyway, I'd use an autonumber and be done with it.

 

[georgedwilkinson]

Ditto.

Always safest to use an artificial (autonumber) PK. Database performance will be better, too, since most DBMSs index integer types much better than they index text types.

 

[pablavo]

Thanks, that's a relief.

What I meant in my badly explained dilemma is that, I was either going to use the long natural PK and put the long natural PK where the line manager names are or just leave the line managers the way they are and still use the natural PK

geez, that is madness, what was I thinking. I've always used autonumbers (almost) and the reason I was thining of this is because of some talk that if one can use a natural PK over autonumber then use it.

I'm with the thought that autonumbers make it easier, especially for any updating that has to be done.

anyway, thanks again for your input.

 

[georgedwilkinson]

some talk that if one can use a natural PK over autonumber then use it.

That's nonsense...don't listen to them and maybe they'll go away.

 

[neileg]

That's nonsense...don't listen to them and maybe they'll go away.

Mmm, don't quite agree, George. It's not my preference but there's a strong body of natural key enthusiasts out there!

 

[dsigner1]

The fun starts when the Manager table is used elsewhere. If you only have these two tables you will get away with anything almost. But an autonumber primary key starts to pay off as soon as the relationships get complicated. Also it is much easier for someone else to take over the database if you always use autonumber PK everywhere.

 

[georgedwilkinson]

The fun starts when the Manager table is used elsewhere. If you only have these two tables you will get away with anything almost. But an autonumber primary key starts to pay off as soon as the relationships get complicated. Also it is much easier for someone else to take over the database if you always use autonumber PK everywhere.

I totally agree. I've had to take over too many crappy systems where the designer used a natural key. It is almost impossible to fix all the hosed up data caused by this. It's really irresponsible for a professional data modeler to do.

 

[Pat Hartman]

I used to be a proponent of "natural" keys and that was probably left over from my mainframe days where "natural" keys were the norm. However, I have to admit that I have turned to the dark side and use autonumbers exclusively. The only downside to this is that when you are looking at a datasheet of a table, there is more of a comfort level in viewing natural keys than meaningless autonumbers.

I forgot to mention that when you use an autonumber PK and there is also a natural key present, you MUST add a unique index for the natural key to enforce business rules. This is a step that too many users forget.

 

[pablavo]

Having listen to everyone here and with my own experience of using auto PKs pretty much exclusivly, I have to say I'll stick with what I'm used to... auto PKs.

I still can't quite get,,, when an auto number is so easy to use in my opinion, why I'd want to use a long natural key, especially when I'm using lookup fields (on the form that is). It just seems quicker to use an autonumber for me and easier to manage.

Someone here posted an Access ten commandments thingy and one of the commandments said:
Don't use Autonumber if the field is meant to have meaning for the users

But the users never have to know anything about PK autonumbers let alone see them.

I'm glad I've had so much input because I'm pro autonumber and I think I'll just continue to use them

Cheers

 

[Pat Hartman]

I just updated post #9 so I wanted to pop the thread.