Implementing a hierarchical select field in advanced modules

pierlo

Say you have a model 'people' and that you need to store a 'role'. Roles are of 2 different types:

1) Technician
2) Professor

and that no.2 'professor' needs to take an additional field ( a foreign key) that says to which 'course' (another model) this professor belongs to (ie 'Design' ,'Philosophy', etc)

I can imagine something like a javascript hack that would show the courses dropdown only if 'professor' was selected. The real question here is more of a general database design kind: how would you translate this structure into a table?
Pseudocode:

table people
-----
id
name
(...)
role -> ENUM : 'technician', 'professor'
course_id -> NULL (foreign key)

and populate course_id only if professor is selected?

I have the feeling this is not so very elegant... any volounteer?

admin

That seems like the simplest approach to me. You could create another table to store the courses associated with the professor and then use one of the model hooks to store to save that away when the role of "Professor" is selected, however, I admittedly would probably use your first approach if all that was required for the professor was a course_id.

pierlo

Thanks for the hint. In fact, the only reason i see why this is not 'elegant' is that you'll end up having tons of NULL values for those that are technicians... but on the other hand, this would make for much faster selects, if i'm not wrong.
If someone can tell me other downsides, please do so!

Thank you!