Determining best database structure
Hi, I am creating my first little application using php and mysql. I am working on the best way to structure my tables in the database.
Scenerio: Each company I work with has the same checklists for each job.
Company 1
Administration: company survey, impact report, technical report, biological report
Preparations: stack review, submit review, comment review, plan a, plan b
Improvements: design plan for a, design plan for b
Final: review final, submit final, approve final
There are 4 Main Tasks (administration, preparations, etc) for each company and there are about 60 subtasks to each Task (I am only showing a few in the above example...surveys, reports, etc.) and then for each subtask, there are 5 things to know...
each subtask's 5 things to know are the same for all
Company 1
Administration >> company survey
date started, person responsible
date finished, person reponsible
notes
Administration >> impact report
date started, person responsible
date finished, person reponsible
notes
etc.
I am mostly confused about how to best structure the tables
Would it be
companys_tbl <<lists all companies
administration_tasks
field_companysurvey
field_companyreports
improvements_tasks
preparations_tasks
final_tasks
if the above is correct, how do I use relationships to get the subtasks information date started, person responsible, date finished, person reponsible, notes
or
companyone_administration
companytwo_administration
companyone_improvements
companytwo_improvements
or
now, I can't figure out how to add the subtasks and each of the subsubs to each area...I am a noobie...reading these mysql books is seemingly confusing and I thought this was going to be easy...not!
I am currently googling mysql tutorials, and the above scenerio seems like it should be easy, but being a noobie, I am somewhat confused.
thanks