0
\$\begingroup\$

On to the next... So I've been sitting with the idea of how to better future proof my database tables when it comes to things like auction houses, inventories and banks.

Q: Is it really necessary to have a primary key value in an auction-house / bank tables? I've already got a link going to the characters ID as well as the item ID.

The reason I'm asking this is because if I use a primary key value of type int or long, what will happen if there are too many entries for type int or long to handle? I'm planning on removing the DB entry as soon as the item is sold, but wouldn't "auto increment" just carry on from the last ID value used?

In short, what would be the down side to not using primary keys in those tables?

Database Design

Improve this question
\$\endgroup\$

2 Answers 2

Reset to default
1
\$\begingroup\$

The debate about natural keys vs synthetic keys is as old as SQL databases themselves. The opinions about this are all over the place and you can read many different opinions about this all around the Internet (if you want to search for some, note that some people also call synthetic keys "artificial keys", "surrogate keys" or "technical keys") .

If the combination of character_id and item_id fully describes every entry in your bank table, then there is no technical reason why you must have an otherwise meaningless id field in addition to that. However, having synthetic keys gives more flexibility in the future, because if you decide to refactor your database and suddenly you do allow multiple entries with the same character_id and item_id in that table, the primary key of the table doesn't need to change which reduces the overall impact of that change. But you will only really feel that impact if you actually use that id field for anything else besides satisfying the requirement for a primary key on every SQL table.

Improve this answer
\$\endgroup\$
13
  • \$\begingroup\$ So as things are now, as long as I delete the table entries if the items should be removed or sold everything should still function perfectly fine without a primary key. Multiple duplicate entries will be possible for the bank table, but as this is only to store the items for a specific character there shouldn't be an issue with it as this table will always be used as a select all to display the characters bank items. \$\endgroup\$
    – Francois Du Toit
    Jul 5, 2019 at 10:14
  • \$\begingroup\$ @FrancoisDuToit Most databases will insist on having a primary key which uniquely identifies every row in a table. But a primary key doesn't need to be just a single field. You can have a primary key which consists of character_id and item_id. \$\endgroup\$
    – Philipp
    Jul 5, 2019 at 10:34
  • \$\begingroup\$ This is where my issue comes in then... I can have more than one of the same item in the bank, thus the character_id and item_id will be the same for both entries... This means I won't have a unique composite primary key for those two entries. So I'll have to have a unique primary key id that auto increments, which brings me back to my original fear of running out of id values if I were to us int or long type. \$\endgroup\$
    – Francois Du Toit
    Jul 5, 2019 at 10:43
  • \$\begingroup\$ Wonder if I maybe shouldn't then just use unsigned long for those table id's? \$\endgroup\$
    – Francois Du Toit
    Jul 5, 2019 at 10:47
  • \$\begingroup\$ I think I'm going about this the wrong way, what if when a character is created, I automatically assign say 60 bank entries (with auto ID's) to that character, but leave the item values empty. Then once the character places an item in the bank it assigns that item ID, etc to the first open bank entry for that character. Once a character then takes out the item, the bank entry is still there, but I then just remove the item details. \$\endgroup\$
    – Francois Du Toit
    Jul 5, 2019 at 11:05
1
\$\begingroup\$

I would use a separate key for every listing. What if a player lists an item, then somehow that item gets back to the same player and gets listed again? Then you can't properly keep a history of transactions.

Also, I wouldn't worry about overflow so much. A long integer in SQL is 8 bytes, which means it can hold up to 2^64 before overflowing. Which is 18,446,744,073,709,551,616 (20 digits).

Improve this answer
\$\endgroup\$

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .