Determining who owns the transferred item in MySQL
I am working on a game where users can buy virtual items with virtual currency and also give those items to other users. We need to be able to track the history of the item (who bought it, who was gifted, etc.) and the current owner. I am struggling with some of the table design.
My thinking is to have this (with a little table simplification and constraints / keys, etc., omitted for the 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,
Since the idea is that when the item is purchased at the same time and created for this item. If the item is gifted, a new record is created in the table and the field is updated . order
owned_item
gift
owner_id
This approach makes it very easy to determine who currently owns a given item. However , it has redundant data and leaves room for data integrity problems. If the "owner_id" field was set incorrectly, we could end up with records that the item that was purchased and donated to B but now inexplicably belongs to C.
How should such a structure be normalized? I decided to exclude the table : owned_item
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 do not like this solution, because finding all the items that some person owns becomes a very complicated operation (find all records of gift X, where the recipient is and no later than the gift record exists for the same order, combined with the find, each record of the order Y where customer is A and there are no gift records for this order), but if that's the right decision, I'll do it.
source to share
Something like this would be a good 3nf circuit for what you want to do.
Entities and transactions are kept in general terms to simplify all relationships between entities and transactions.
-- An Entity is a person or business, as a party in a 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 are each an instance of an item-type
-- e.g. the item "Steve broom" may be an item of type "broom"
CREATE TABLE item_type (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR NOT NULL
);
-- Non-unique item, instance of an item-type owned by an entity
CREATE TABLE item (
id INT NOT NULL AUTO_INCREMENT,
-- optionally include owner_id as a quick-reference to the current owner of the item
owner_id INT NULL REFERENCES entity (id),
-- FK to unique item types, e.g. "broom"
item_type_id INT NOT NULL REFERENCES item_type (id),
-- possible description, e.g. "Steve broom"
description VARCHAR NOT NULL
);
-- A transaction is a sale, gift, or other method of transferrence
-- of an item between entities. Transaction is a bad name, because
-- it a reserved word. That why it encased in ticks.
-- You'd probably be better off choosing a different generic name
CREATE TABLE `transaction` (
id
-- transaction_type can be NULL in cases of origination with entity
transaction_type ENUM('sale', 'gift') NULL,
-- NULL in cases of origination with an entity
from_entity_id INT NULL REFERENCES entity (id),
to_entity_id INT NOT NULL REFERENCES entity (id),
-- amount can be 0 in cases of gifts
amount DECIMAL(9,2) UNSIGNED NOT NULL DEFAULT 0
);
The gift transaction will have a count of 0 (or NULL if you want to make it zero).
An operation "origin" (for example, something was done or found) will not have a transaction type and 0 amount.
To find out who is the current owner of an item, use a view that retrieves the last "to_entity_id" for that item in the transaction table, for example:
SELECT
e.name
FROM entity AS e
INNER JOIN `transaction` AS t ON e.id = t.to_entity_id
INNER JOIN
(SELECT MAX(id) AS id
FROM `transaction`
WHERE item_id = 5) AS tx ON tx.id = t.id
Alternatively, you can also store the owner_id in a table of items (see note above in schema). This would be a little overkill and would require updating this table for every transaction, but save a lot of expensive queries to find out who owns.
source to share