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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql