OK – if you’re not into Django (the Python webframework), or you don’t know know anyone who does, look away now. Otherwise, I really need your help. Hell – even if you know nothing about Django, but know some database stuff, you might have some insight. Either way, it’s important 
I’m trying to create a Rota system – you know, like the bit of paper that you stuck on the fridge when you were student, identifying who was supposed to do what each week? So I will have a table of users, let’s call them Rob, Bob, Mark, Mary, Lisa, and Rupert (for arguments sake). And a table of jobs (washing up, toilet cleaning, hoovering sitting room, etc). These 2 tables need to be linked in some way to indicate who can do which jobs. (Mary gets bad hay-fever, so likes to avoid the dusty hoovering; Rupert is allergic to fairy liquid; etc) Now – this can either be a list of names associate with the Job, or a list of Jobs associated with the User. I’m not overly fussy either way.
Each week there needs to be a rota – the rota should be dated, and contain each of the jobs, and which person is doing the job. But (and this is the big but) when selecting the user for a particular job, you should only have to choose from the users that can do this job.
So – 2 questions:
- What is the best table structure to do this?
- What is the best way to represent this in Django? I am beginning to wonder if I will actually need to do a custom admin page for this model, in order to achieve the desired result.
Any help would be gratefully appreciated.
mrBen
Three tables:
person (id int, name varchar)
job (id int, title varchar)
person2job (person int, job int)
Then add one row per allowed person/job combination in person2job.
All people who can do, say, “washing up” is then expressed by
select p.name from job j
inner join person2job p2j on j.id = p2j.job
inner join person p on p2j.person = p.id
where j.name = ‘washing up’
or if you already know the job id rather than its name, that simplifies to
select p.name from person2job p2j
inner join person p on p2j.person = p.id
where p2j.id = $known_id
NFI about Django, btw. SQLObject annoys the hell out of me. Having said this, this sort of simple-relationship thing is supposed to be what it’s good at, isn’t it?
The hardest part is working out whether or not I have to custom build a section for the admin tool, which (for the most part) creates really sensible defaults.