What should I use as my Primary Key

[arr0w]

If i have a table of characters/people, is it better to use a AutoNumber ID field as the Primary Key, or is it okay to just use the names? Can I get an example of why or why not to use either of these in this case?

Also, I created separate tables for males and females and just realized that the second one is continuing the PK ID field numbering from the first, as if they're connected somehow. I copy/pasted the table and renamed it. Is this alright or should I start that one over?

 

[gemma-the-husky]

1. I doubt if anyone would recommend a separate table for males/females. just have one table, and add a yes/no field for male, say.

2. I would add an autonumber key. The important thing is that in other tables related to this table, they also store this autonumber key. That is also why you only have one table for male and female - otherwise you will get in an awful tangle.

The problem with using names is that
a) the forename/surname may not be sufficient - you may have duplicates and
b) names can change - people get married etc

In addition to which, a numeric key is more efficient than a text key.

Finally, do not worry about the value of the autonumber. It isn't there to give you an intact sequence. If you need an intact sequence, you need a different technique.

 

[ButtonMoon]

If i have a table of characters/people, is it better to use a AutoNumber ID field as the Primary Key, or is it okay to just use the names? Can I get an example of why or why not to use either of these in this case?

What identifies a Character/Person in the business domain? That is the requirement you should aim to fulfill in your database design. Names of people don't typically make good identifiers because different people often share the same names. That said, there certainly are scenarios in which names do make useful indentifiers. Without knowing the requirement no one is in a position to answer your question properly.

Auto-number columns are purely a technical feature and are usually best used for surrogate keys only. They are not a substitute for or an alternative to a natural key. Determine what you will use for a natural key first and then decide on the details of a technical implementation to support that.

 

[arr0w]

Thanks for the answers guys! As to all the comments about that names arent/wouldnt be good identifiers; i do know what you mean, however in my case, each one will be unique. also, im reluctant about having them all on one table because there are several categories for the males that the females wont have info for.

 

[namliam]

Thanks for the answers guys! As to all the comments about that names arent/wouldnt be good identifiers; i do know what you mean, however in my case, each one will be unique. also, im reluctant about having them all on one table because there are several categories for the males that the females wont have info for.

Categories shouldnt be columns in your database which is what you have right now (just an educated guess from your response)
Instead you should be able to dynamicaly add records inta a categories table which has PersonID, CatName, CatValue as columns, you can add as many records/categories per person as you see fit.