Trying to understand how to use the SET Type for a small app I'm working on and could do with any advice anyone can offer.
Probably easiest if I describe what I'm doing.
Writing an application for an "On Call" rota. Have two tables, one listing the staff available to be On Call, the other for dates to assign the staff to.
On a given day, the on call staff need to be listed in order of priority - who should be called first, second etc (Priority 1, 2, 3... up to 10 max).
Looking at the MySQL columns, the SET type seems to be useful for what I'm trying to do, to prevent me needing to create 10 different columns for each Priority Level. The MySQL reference manual has left me only partially enlightened though.
What I'm thinking is I assign Staff, using their ID (the index column in the Staff table) and placing them in a SET column (call it Priority) in the dates table, the first member of that column being Priority 1, the second 2 and so on.
1. First of all, is this a good way to solve this problem?
2. How do I INSERT into the SET column?
3. How do I UPDATE the SET column?
4. For Queries, would this give me the first member of the SET column? "SELECT * FROM Dates WHERE Priority & 1;". What are the general ins and outs of querying SET columns?