Hi there!
I am trying to solve a problem, but I am totally stucked. I have a
database with users, cards (260) and decks. The users can have cards
(a lot of), their cards can be in different decks or in trade state. I
don't want to store the cards one by one, so one user has only one row
for a specific card type, and this row indicates the total number of
cards what the user have, and how many card has in decks and how many
in trade state.
Table for users:
Table "public.users"
Column | Type |
--------------------+-----------------------------+
id | bigint |
name | character varying(20) |
Table for cards:
Table "public.cards"
Column | Type |
-----------------+-----------------------+
id | integer |
name | character varying(50) |
Table for connect users to cards:
Table "public.users_has_cards"
Column | Type |
----------+---------+
user_id | bigint |
card_id | integer |
total | integer |
decks | integer |
auctions | integer |
total, decks, auctions are the filed which are storing the card
numbers which the user have.
Every user can have several deck assembled. Every card can belong to
multiple decks independently.
Table for decks and for the cards in the decks:
Table "public.users_has_cards"
Column | Type |
----------+---------+
user_id | bigint |
card_id | integer |
total | integer |
decks | integer |
auctions | integer |
Table "public.decks_has_cards"
Column | Type | Modifiers
---------+----------+-----------
deck_id | bigint | not null
card_id | integer | not null
piece | smallint |
Of course decks_has_cards.card_id is a foreign key from
users_has_cards. When a user assembling a deck, he can use from his
available card. Available card count from a specific type is counted
from users_has_cards.total - users_has_cards.auctions. The user picks
cards from available cards, and then save the deck.
users_has_cards.decks stores the biggest number of cards which the
user has in one of his decks. With this it can be determined how many
cards of the user can be put to trade state.
The task is to hold this in a consistent state and to grant
users_has_cards.auctions + users_has_cards.decks never can be higher
then users_has_cards.total. It should be also granted that
users_has_cards.total - users_has_cards.auctions number of cards from
a specific type can be in any decks.
The deck assembly input comes from web, and parsed with PHP, so it is
absolutely not trusted.
I am started to write a PL/PgSQL function to handle the save of a
deck, but it seems unable to solve this issue. I don't know how to go
forward, so any idea is appreciated.
Matyee