Artificial Primary keys

I'm new to designing any relational database but that's what I have to do now.
Broadly, it's a 'personnel' type database; employees put on assignments which themselves are jobs that are in a specific location and they have a start- and end-date.

The identifier for an employee is an arbitrarily chosen 'next avaliable number', because that's an easy way to guarantee uniqueness.

The primary key for the Assignment table will be 'assignment-id' -- using the same arbitrary method of another 'next available number'. However, the real-world unique id for an Assignment is 'employee-id + start-date'. Other people's thinking in this area is that a short, numeric key is easier to maintain (marginally) and it will perform much better for the joins that are to come.

Do you have any comments on the use of 'artificial' keys like this, rather than using the actual attributes of an assignment?

For my part, I feel unsure that it's worth imposing this on the design without knowing that there really are maintenance/coding/performance advantages worth having; but I don't know -- hence this post.

Any general comments and items of advice will be welcome; specific ones even more so ....

Regards

Michael Russell Received on Wed Jan 23 2002 - 07:29:06 CST