Hi All,

newbie here with a quick question.

I am designing a database for a game I am making and just noticed something with the tables I am creating.

I have a table holding all the weapons in the game with:
- weapon_id
- weapon_name
- etc.....

now weapon_name is always going to be unique. Is there really a need for a weapon_id then when I could use the weapon_name as the primary key? Or is there some preformance thing involved with using an integer as the primary_key as opposed to a string?

Thanks

Uhmm since nobody else is replying I guess I will

From what I gather (I'm new to this too) there will theoretically be a difference in performance, however it will be so small that it will be unnoticeable in most cases, especially small tables like what I'm assuming you're doing. So sure, you can use weapon_name, but I guess for scalability's/speed's sake you could use a primary integer key.

One of the considerations is how often will the key be updated. By implementing a surrogate key, you can avoid cascading updates.

There are good reasons for using the id, and good reasons for avoiding it. You have to find which makes the best sense for this application.

Surrogate keys (SKs) are arbitrary. SKs are created for the use of the application, often by the database itself. The user shouldn't ever see or even know that the SK exists, so they should have no desire to change the value of the SK which would cause cascades. The SK should be near optimal in terms of size (typically very small and easy for the application to manipulate), so it should cause much less I/O and database growth when it is used as a Foreign Key (FK).

The Natural Key (NK, in your example the weapon name) is easier for people to understand. An NK is the "handle" that people expect to use to manage data, so it makes relationships easier to see.

All told, I'm very much in favor of SKs. They allow the application to run faster, require less storage, and avoid all kinds of complexity. In my book, that's a good thing. There could be cases where the use of the NK would be better, but I've never seen one of those cases!

-PatP

If you do use a surrogate key, be sure to still declare a unique constraint on the natural key; otherwise you will get duplicate rows at some point for sure.

How? Surley it adds to the storage, another column of data.


I think if the NK is pretty small, and logical to both human view and db view, NK would be better than having another column for an SK. eq Stock codes, cant see any reason why an SK would be needed here.

With the weapons table an SK might be a good idea because parssing an integer rather than an string is faster, and in game design speed is the essence. If updating the table is frequent, then make sure you have a constraint between the SK & NK.

TableA
--------
NK(col1, col2) UNIQUE
SK(ID) PRIMARY KEY

TableB
-------
ID PRIMARY KEY
taID REFERENCES tableA(ID)

This is because your NK is made from two columns, aka composite. Or am I not understanding this at all? Can you explain further?