Good Database Design: Should I use an ID field?

[Piper]

I have a table which contains employee names (NAMES). NAMES is linked to several other tables, for instance, ACTIONS records which employee took an action and uses NAMES to provide a lookup box.

I can design my NAMES table in one of two ways: ID|Name, or just Name. What are the pros and cons of each way? There's pretty much no chance I'll ever have 2 employees with the same name. I feel like using IDs is the best way, but can't really justify why it's worth the hassle of the extra field. My database is not huge - probably no more than 500,000 records at its largest. Storing the ID instead of the name means the NAMES table has to be pulled in to queries to show who took an action, instead of seeing all the information you need when you simply open the ACTIONS table.

 

[John Big Booty]

Welcome to the forum.

Use an ID (assigned from AutoNumber) and make that the Primary Key (PK) for your Table.

 

[plog]

There's pretty much no chance..

Use numeric ID's until you can say "there's no chance". And then you probably should use them still.

 

[Galaxiom]

People can change their names.

BTW Name is a reserved word. Don't use it for any object or field name.

 

[Swemebegur]

You are absolutely certain - not just likely - to have duplicated names sooner or later. With peoples' names you should always use a separate ID, not a name. And surely it isn't really a hassle to write a query joining two tables?

 

[MSAccessRookie]

I have a table which contains employee names (NAMES). NAMES is linked to several other tables, for instance, ACTIONS records which employee took an action and uses NAMES to provide a lookup box.

I can design my NAMES table in one of two ways: ID|Name, or just Name. What are the pros and cons of each way? There's pretty much no chance I'll ever have 2 employees with the same name. I feel like using IDs is the best way, but can't really justify why it's worth the hassle of the extra field. My database is not huge - probably no more than 500,000 records at its largest. Storing the ID instead of the name means the NAMES table has to be pulled in to queries to show who took an action, instead of seeing all the information you need when you simply open the ACTIONS table.

An essential point in the decision whether or not to use IDs is the Human Factor. For instance, if I were entering aa record for you, I might enter it as "Piper", but I could enter it as "Pipper". Even though I intended the records to both be for you, Access would count them as two different people. If I assigned "Piper" an ID, and selected names from a list of available names, then that could not happen. Of course, it also means that you would need a way to add names to the list, but that is another issue.

Other aspects to consider:

• A Number Field is small (usually 2 or 4 bytes), and a String is usually much larger, so using the names as Foreign Keys in other Tables is less efficient
• Indexes handle Numbers far more efficiently than Strings

-- Rookie