hi

i'm currently working on the design of an application using mysql and php-4
the goal of the application is to record job requests from users
(ex : user1's office door is broken, user2's phone is out of order, user3 needs a new chair/desk etc ..)
my ERD is quite simple and has 2 entities:

user ----> job

where 1 user can request from 0, to N jobs and 1 job depends on 1 user

problem is the html form we need contains a bunch of checkboxes which i have to record the status in the database, as booleans (around 20 checkboxes) like job concerns phone, fax, network, chair, desk etc .. 1 or more checkboxes can be checked for 1 job

i'd rather not have 1 field for each checkbox in my job table, i was thinking of creating a specific table for all the checkboxes, but for that i would need a joint table between the checkboxes table and the job table which i think would be theorically good but practically not very optimized

does anyone have an idea of the database schema i could use for this ?

thanks a lot !

I think your proposed solution is both theoretically sound and practically optimized.

This uses an RDMS design structure referred to as a Joining Table or Linking Table to maintain/resolve a many-to-many relationship. Heres a tutorial to help you understand the concept. How to handle a Many-to-Many relationship with PHP and MySQL By Tony Marston.

If you have a table to maintain the list of task items, you could programatically build your selection lists, or checkboxes on the form based on the items in the table. This would make the form self updating, so if you add an item in the table it would appear on the form without having to manually update the form. In your task items table include a column for task description and task Title. You can use these fields in the form generation process.


/*