An "Item" is a super-class of other things, like "Weapon" or "Armour".
The obvious, Relational way to handle this is to create a Table for every "Entity" that can exist on its own. A weapon exists even if no-one is carrying it, a piece of armour even if no-one's wearing it. An Item is a more "logical" construct (abstraction) that generalises either type of thing.
select *
from Weapons ;
+----+-------------+--------+----------+
| id | name | damage | accuracy |
+----+-------------+--------+----------+
| 1 | basic sword | 16 | 10 |
| 2 | slingshot | 1 | 1 |
| 3 | R.P.G. | 12000 | 9 |
+----+-------------+--------+----------+
select *
from Armours ;
+----+-------------+---------+-------+
| id | name | defence | class |
+----+-------------+---------+-------+
| 11 | leather | 7 | Hmmm |
| 22 | plate | 12 | Good |
| 33 | force-field | 999999 | Wow! |
+----+-------------+---------+-------+
select *
from Items ;
+------+-----------+---------+
| id | item_type | item_id |
+------+-----------+---------+
| 1111 | Weapon | 1 |
| 2222 | Armour | 33 |
+------+-----------+---------+
select w.name
, w.damage
from items i
inner join weapons w
on i.item_id = w.id
where i.id = 1111 ;
+-------------+--------+
| name | damage |
+-------------+--------+
| basic sword | 16 |
+-------------+--------+
As they stand, these structures are easy to understand because each one describes a "real" thing and relationships between tables mimic the relationships between the things they represent.
It also means that your application is
coded with this in mind. You have to join Items to Weapons when dealing with a weapon, to Armours when dealing with Armour. Again, it makes it obvious what you're looking at.
That said, it's not the most
flexible design.
If you really,
really need to be able to add [almost]
any "property" to
any item, then you
might consider an "Entity-Attribute-Value" mode. It's more complicated to work with, [far] less obvious to look at and, generally speaking performs
very poorly compared to the model I've described.
Regards, Phill W.