What's the best algorithm to implement a job collection?

This is what I'm trying to achieve. I have a set of 50 different jobs (by class I mean, soldier, mage, monk, etc)

Each user randomly gets a card each time he/she completes a quest. So at certain point you can have a sol开发者_如何转开发dier and a monk, but a mage will be missing. That means your collection will look like

Soldier    Yes
Mage       No
Monk       Yes

At anytime you can get the same card (you can have 10 soldiers, but for your collection is only a yes or no)

At anytime you can drop the job. But you keep the job "collected" Therefore if you drop a Soldier the collection will look like this

Soldier    Yes
Mage       No
Monk       Yes

If you notice, even if I don't have a Soldier right now. I have already "collected" that job.

I was thinking about what would be the best way to implement this in MySQL and PHP?

Mi ideas right now are 2 1. create a new table collection and keep one record for each job you get. 2. create just one field with pipes as separators and implode/explode results

---

Why don't you use a single bitmask column.

Say Soldier = 1, Mage = 2, Monk = 4.

Thus if you have only mage and monk the value would be 2 | 4 = 6.

This way you allow yourself to easily add different classes later, without the need to change the schema.

---

Unless you have serious peformance issues a separate table should be fine. It has the extra benefits that you can later add attributes to jobs if needed (eg: beginner/master, experience points, etc..). Using bitmasks is not flexible and you can end up with an ugly db scheme later. It also doesn't allow you to do quick lookups across users, eg: if you need all the users who have soldier and monk jobs.

---

I ended up adding a field named "deleted" in my table. 0 == exists, 1 == deleted That way I can have an story of all the cards in the game. It might add up a few rows, though.

What do you think?

it looks like http://apps.facebook.com/talesofm/jobs (if you have the job, it will be colored, if you do not have it, it will be black and white)