i have 2 entities storage, items. items stored in storages. items have different types. example, item types resources, weapon. weapons have unique item instance characteristics "damaged".
resources doesn't have unique instance characteristics, , can stacked existing instance increasing count.
i have multiples way it:
save in 1 table:
storage_items(id, item_id, count, damaged) , create partial index onitem_idcondition count not nullseparate 2 tables types (
storage_resources,storage_weapons)create 2 tables
storage_items(storage_items_id, item_id,count) ,items_properties(id, storage_items_id, damaged).
one way have different subtypes linked common table use type codes distinguish link. example:
create table storage( id serial, itemtype char( 1 ) not null, ..., -- fields common items constraint ck_storagetype check itemtype in( 'r', 'w' ), primary key( id, itemtype ) ); the id field unique, may need or want have pk itself. can have instead:
create table storage( id serial, itemtype char( 1 ), ..., -- fields common items constraint ck_storagetype check itemtype in( 'r', 'w' ), primary key( id ), constraint uq_iditem unique( id, itemtype ) ); either way, create separate table each type of item:
create table resourceitems( id int not null, itemtype char( 1 ) not null, ..., -- fields unique resource items constraint resourceitemtype check( itemtype = 'r' ), primary key( id, itemtype ), constraint fk_resourceitem_storage( id, itemtype ) references storage( id, itemtype ) ); create table weaponitems( id int not null, itemtype char( 1 ) not null, ..., -- fields unique weapon items constraint weaponitemtype check( itemtype = 'w' ), primary key( id, itemtype ), constraint fk_weaponitem_storage( id, itemtype ) references storage( id, itemtype ) ); so must designate storage entries either r or w type. resource entries must have entry in storage defined r , weapon entries must have storage entry defined w. allows have different types of items while keeping them firmly segregated, maintaining data integrity.
