sql - Determining who owns a transferrable item in MySQL -

i working on game users can buy virtual items virtual currency, , give items away other users. need able track history of item (who bought it, gifted to, etc) , current owner. i'm struggling of table design.

my thinking have (with table design simplified , constraints/keys/etc omitted space):

table order id                 int not null auto_increment, buyer_id           int not null, paid               int unsigned not null, owned_item_id     int not null, ------------------------------------------------------ table owned_item id                 int not null auto_increment, store_product_id   int not null, owner_id           int not null, ------------------------------------------------------ table gift id                 int not null, giver_id           int not null, receiver_id        int not null, owned_item_id      int not null, 

with idea being when item purchased, both order , owned_item created item. if item gifted, new entry in gift table created , owner_id field updated.

this approach makes easy determine owns given item. however, has redundant data , leaves room problems data integrity. if 'owner_id' field set incorrectly, might end records item purchased , gifted b inexplicably owned c.

how should such structure normalized? i've considered eliminating owned_item table:

table order id                 int not null auto_increment, buyer_id           int not null, paid               int unsigned not null, product_id         int not null, ------------------------------------------------------ table gift id                 int not null, giver_id           int not null, receiver_id        int not null, order_id           int not null, 

i don't solution because finding of items person owns becomes complex operation (find every gift record x recipient , no later gift record exists same order, combined find every order record y buyer , no gift records exist order) if that's correct solution i'll make do.

something 3nf schema you're looking do.

entities , transactions kept generic in order allow entity / transaction relationships simplified.

-- entity person or business, party in transaction create table entity ( id                  int not null auto_increment, entity_type         enum('store', 'person') not null name                varchar not null );  -- unique item types - items each instance of item-type -- e.g. item "steve's broom" may item of type "broom" create table item_type ( id                  int not null auto_increment, name                varchar not null );  -- non-unique item, instance of item-type owned entity create table item ( id                  int not null auto_increment, -- optionally include owner_id quick-reference current owner of item owner_id            int null references entity (id),  -- fk unique item types, e.g. "broom" item_type_id        int not null references item_type (id),  -- possible description, e.g. "steve's broom" description         varchar not null  );  -- transaction sale, gift, or other method of transferrence -- of item between entities.  transaction bad name, because -- it's reserved word.  that's why it's encased in ticks. -- you'd better off choosing different generic name create table `transaction` ( id -- transaction_type can null in cases of origination entity transaction_type    enum('sale', 'gift') null, -- null in cases of origination entity from_entity_id      int null references entity (id),  to_entity_id        int not null references entity (id), -- amount can 0 in cases of gifts amount              decimal(9,2) unsigned not null default 0 ); 

a "gift" transaction have amount of 0 (or null, if wanted make nullable).

an "origin" transaction (e.g. made, or found) have no transaction_type, , 0 amount.

to know current owner of item is, use view retrieves last "to_entity_id" item in transaction table, e.g.:

select      e.name entity e inner join `transaction` t on e.id = t.to_entity_id inner join     (select max(id) id     `transaction`     item_id = 5) tx on tx.id = t.id 

alternatively, store owner_id in item table (see note above in schema). little redundant, , require updating table on each transaction, save lot of expensive queries know owns what.