Hi,
take a breather, this is a strictly education question (no code needed).
One of the common things I foresee dealing with data is where a table covers multiple categories of entries, but for which we want to search on a single field.
For example we might have a resources table for a school. Someone can be a teacher or a student (or a vendor or a customer). It is possible from a logical standpoint someone could be a teacher AND a student (and/or both a vendor and a customer).
We want to be able to find all students, and return them, and all teachers, and return them. We might also want to know that they are in another category as well. Finally we might want to search for "All students and teachers" and need to list them both for the purpose of grouping.
Example: we have 30 students and 30 teachers.
But we only have 59 people total; one of them is both a student and a teacher. We want to make sure our group by queries show that we have 30 and 30, not 30 and 29!
I had thought about using a SET type of field for the Category, so it could contain a value like 'student,teacher' if both are true.
Again, this situation is quite common.
The obvious dilemma is that 1) they all will have a lot of common data (address info, phone, email, comments, ..) 2) It would be elegant if we could have them all in 1 table 3) But if we do this, I think the queries might be more difficult, and using SET we may lose the benefit of some compact indexing. Plus primary-foreign key relationships might get a little murky.
What would YOU do?
Another case, by the way, is a company that deals with architects, engineers, develpers, etc. Suppose all this went into a resources table. Another table, projects, lists the name of a product, the Architect_ID as ID from resources, the Developer_ID as another ID from resources, etc..
Thank you again for reading this post...
Sam Fullman
Compass Point Media
