Hey,

I'm working a while now with MySQL but i'm still not sure what to do with the primary keys in some cases.

For example i have a table inventory for each player at each station so the table looks like this ->

[inventory]
-stationID
-userID
-itemID
-amount

On this table i could make stationID, userID and itemID together the primary key. Would it be wise to make an extra collumn inventoryID and make just that the primare key? Or would that be useless as i would never search on inventoryID?

Now let's say i have split up my items in groups like weapons, armor and items the inventory table would look like this ->

[inventory]
-stationID
-userID
-weaponID
-armorID
-itemID
-amount

Same question i guess but it might be wiser to use a inventoryID as a primary key here maybe.

Quote:Original post by menyo
For example i have a table inventory for each player at each station so the table looks like this ->

[inventory]
-stationID
-userID
-itemID
-amount

On this table i could make stationID, userID and itemID together the primary key. Would it be wise to make an extra collumn inventoryID and make just that the primare key? Or would that be useless as i would never search on inventoryID?

I think you should go with the one 'inventoryID'. The problem with creating a composite primary key using those other data columns is that all other tables with foreign keys into this table will also need to have those same three columns. But it only really takes a single value to act as that reference. IMHO it's a lot easier to have a single column act as the primary key, even if that's the only purpose it serves, because every other table only needs that one column. Plus the data will only be in one place. If you want those three columns to be unique, then use a separate UNIQUE constraint.

Quote:Now let's say i have split up my items in groups like weapons, armor and items the inventory table would look like this ->

[inventory]
-stationID
-userID
-weaponID
-armorID
-itemID
-amount

Same question i guess but it might be wiser to use a inventoryID as a primary key here maybe.

This is a perfect example of how a single primary key column makes your life easier. It's automatically forwards-compatible with any other changes to the table. Had you used the aforementioned composite primary key and then added these new columns, you'd suddenly be stuck fixing up every other table with a foreign key into this one. Not very fun.

That being said, I'm not sure why you'd need three different ID columns, since they'd all be referring to items and I'm assuming only one will be non-NULL at a time. That information probably already exists in another table somewhere, so no need to duplicate it here and require more constraints to maintain the referential integrity.

Well all those ID's refer to the items in the DB. A weapon has damage but a armor has defense so they go into separate tables.

I'm still not quite sure what you mean by your post. Yes i should use the inventoryID but should i use it like this ->

[inventory]
-inventoryID
-stationID
-userID
-weaponID
-armorID
-itemID
-amount

Or should i use it like this ->
[inventory]
-inventoryID
-stationID
-userID

[weaponinventory]
-inventoryID
-weaponID
-amount

[armorinventory]
-inventoryID
-armorID
-amount

etc.?

I could also use it like this but i don't know if thats smart to do as i would query text most of the time ->
[inventory]
-inventoryID
-stationID
-userID
-itemtype (holds text like "weapon" or "armor")
-itemID (corresponds to ID of type given in -itemtype)

ofcourse i could give each different item a number and use that number for itemtype.

Yes, if you have multiple tables for different types of items, then you might make your inventory table something like:

INT item_id
VARCHAR(8) item_type -- this can be one of many different field types, actually.

Then create a key on both columns. You do this because the ID by itself is useless and the type by itself is also useless. Together, they identify the product.

Also consider making your items generic and putting them all in one table. You may find that certain items have crossover effects. Perhaps a sword has a defense rating. Does a shield have an attack rating? Does it have spikes on, it? Things like that.

From previous experience, put similar thing in different tables is best avoided if possible. It'll mean headaches later on.

Quote:Original post by menyo
I could also use it like this but i don't know if thats smart to do as i would query text most of the time ->
[inventory]
-inventoryID
-stationID
-userID
-itemtype (holds text like "weapon" or "armor")
-itemID (corresponds to ID of type given in -itemtype)

ofcourse i could give each different item a number and use that number for itemtype.

Yes, give each item type an ID instead of a string. However the inventory table wouldn't need to store that type because you'll have a separate items table that knows this information. This is what I was thinking (doesn't use proper MySQL syntax but you'll get the idea):

[item_types]
-item_type_id BIGINT PRIMARY KEY
-item_type_description NVARCHAR(255)

[items]
-item_id BIGINT PRIMARY KEY
-item_type_id BIGINT FOREIGN KEY REFERENCES item_types(item_type_id)
-item_description NVARCHAR(255)

[weapon_items]
-item_id BIGINT FOREIGN KEY REFERENCES items(item_id)
-clip_size BIGINT
-rpm BIGINT
-ammo_type FOREIGN KEY REFERENCES ammo_types(ammo_type_id)

[armor_items]
-item_id BIGINT FOREIGN KEY REFERENCES items(item_id)
-armor NUMERIC(38,4)
-durability NUMERIC(38,4)
-weight NUMERIC(38,4)
-protection_type FOREIGN KEY REFERENCES protection_types(protection_type_id)

[inventory]
-inventoryID BIGINT PRIMARY KEY
-stationID BIGINT FOREIGN KEY REFERENCES stations(stationID)
-userID BIGINT FOREIGN KEY REFERENCES users(userID)
-itemID BIGINT FOREIGN KEY REFERENCES items(item_id)

Worth reading: Choosing a Primary Key: Natural or Surrogate? Everywhere it says 'business', read the word 'game' instead. ;)