纠结数据库记录使用数还是未使用数的数据库设计

Question

sql设计sql

Answer 1

把表分开比较好
卡表 card
card_id      |   name  
1                 |    1卡
2                 |    2卡商品表 goods
goods_id  |   name
1                 |   A商品
2                 |   B商品
3                 |   C商品关联表  relation（column1总数，column2使用数）
card_id      |   goods_id     |     column1     |     column2
1                 |   1                    |     8                   |     3
1                 |   2                    |     2                   |     2
1                 |   3                    |     4                   |     0
2                 |   1                    |     7                   |     1
2                 |   2                    |     9                   |     8
简单说明一下：
上表的意思就是1卡的A商品有8个使用了3个，有B商品2个使用了2个，有C商品4个没有使用。
2卡也是一样的意思，如果有其他商品只要以以上格式往下不断加记录即可，如加2可有C商品2没有使用就是2|3|2|0。下面查询看看-- 查1卡中全部商品的总数、使用数、未使用数
select sum(relation.column1),sum(relation.column2),(sum(relation.column1)-sum(relation.column2))  from card 
left join relation on relation.card_id=card.card_id 
left join goods on goods.goods_id=relation.goods_id
where card.name='1卡';-- 查1卡中A商品的总数、使用数、未使用数
select ifnull(relation.column1,0),ifnull(relation.column2,0),(ifnull(relation.column1,0)-ifnull(relation.column2,0))  from card 
left join relation on relation.card_id=card.card_id 
left join goods on goods.goods_id=relation.goods_id and goods.name='A商品' 
where card.name='1卡';
其他查询也差不多，比如查2卡就吧1卡上面的1卡换成2卡，查B商品就把上面的A商品换成B商品
似乎复杂了不少，但业务逻辑比较清晰，希望楼主能看懂吧

Answer 2

忘了name是mysql关键字，我修改下语句-- 查1卡中全部商品的总数、使用数、未使用数
select sum(relation.column1),sum(relation.column2),(sum(relation.column1)-sum(relation.column2))  from card 
left join relation on relation.card_id=card.card_id 
left join goods on goods.goods_id=relation.goods_id
where card.`name`='1卡';
 
-- 查1卡中A商品的总数、使用数、未使用数
select ifnull(relation.column1,0),ifnull(relation.column2,0),(ifnull(relation.column1,0)-ifnull(relation.column2,0))  from card 
left join relation on relation.card_id=card.card_id 
left join goods on goods.goods_id=relation.goods_id and goods.`name`='A商品' 
where card.`name`='1卡';