Use of primary keys

[Homegrownandy]

I'm in the development stage of creating a new database. I've never used access before but I have a concern about how the end user will use the system.



If I give each "job" a job ID in the interest of keeping this unique to the job. How will an end user find the information they need?

They might only know other information regarding the job. Is this an issue?

I understand the importance of it being developed like this. But again, from an end users perspective how could they find records easily?

Basic question I know. But look forward to hearing some responses.

Andy.

[ButtonMoon]

Keys provide users with a guaranteed way to identify data in the system. Design keys that are simple, stable (unlikely to change) and familiar to users (or that you expect them to become familiar with in day to day use). If users don't know the key value then of course it's still possible for them to search for data based on other non-key attributes as well.


It also can be a good idea to provide one or more alternative keys for a table. For example a project might be identified not just by a unique project number but also by a unique name. Make sure users know what the keys are and that they match their actual requirements.

[Homegrownandy]

So using a staff list as an example with two members of staff with the same name. I've assigned each member with a unique ID.

I could print out A list of staff with the ID next to the name as a reference. Or in the system the user could type in first name and pick from multiple options.

The second option is more desirable but as I'm a total novice I'm unsure if its possible or if it's the correct way to go about this.

Just trying to get a clear picture of what I'm going to be doing.

Thanks! andy,

[ButtonMoon]

I think the questions you are asking relate to the actual user experience rather than just the database design.

As a user of a staff database I might have different use cases. In the first case, I need to look up a member of staff by staff ID where that information is already supplied. For example, where I work my staff ID is printed on my payslip and if I have a query with the payroll department they would probably ask me to tell them my ID and not just my name. Obviously ID would be a key in your database and users should be able to retrieve an employee record based on that.

A second use case is where a user doesn't have the ID and wants to search for employees based on some other information, e.g. name and department or date of birth. To do that, you could provide a search form that allows users to search based on any fields in the database. It is then up to them to provide enough information to find a specific employee. You don't need name, department or date of birth to be a key in order to do that.

Another use case might be to find an employee based on some specific unique identifier other than staff ID. For example, employee login name. In that case login name might also be a key, enforced with a unique index in the database.

I suggest you talk to your users about these kinds of use cases and understand first what their needs are.

[Homegrownandy]

Okay, I have been looking at database design tutorials all day. Its just nice to hear different opinions. Thanks for the help.

Ill probably have more questions to post on here in the upcoming weeks.

cheers! Andy.

[ssanfu]

You might read these:

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm

[Homegrownandy]

You might read these:

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm

Thanks a lot Steve, Great help.

andy