Build a reporting website.
Database Tables (Users, Cities, Departments, Reports, Report_Types, City_Report_Type)
Users will be able to create and manage user profiles (User_ID, Name, Address, City, State, Zip, Phone, Email, UserName, User_Type, User_Notification_Type )
The site admin user will manage the creation of cities, and each city user or the admin user will be able to create departments that are linked to the city.
The Admin, City Admin, or Department Admin user will manage the Department profile.
Cities (City_ID, User_ID, Name, Address, City, State, Zip, Phone, Email, City_Lat, City_Long, City_Description, City_Mayor, City_Logo) and
Departments (Department_ID, City_ID, User_ID, Department_Name, Department_Address, Department_City, Department_State, Department_Zip, Department_Phone, Department_Email, Department_Logo, Department_Type, Department_Status, Department_Rating, Department_Response_Time, Department_Notification_Type)
Report_Types (Report_Type_ID, Report_Type_Label, Report_Type_Description, Report_Type_Icon)
Report (Report_ID, Report_Department_ID, Report_User_ID, Report_Title, Report_Comments, Report_Create_Date(TimeStamp), Report_Type, Report_Status, Report_Lat, Report_Long, Report_Address, Report_City, Report_State, Report_Zip, Report_Action_Desired, Report_Public)
Anyone will be able to create a report by fill out a form that has the City information based on the location of the report, and the Department will be populated by a pull down list of report types (Street issues will be delivered to the Streets Department etc.) The user can make his post public or private, and it will be listed in the control panel for the Department that the report type goes to. The user will be able to use IOS6 to upload photos, and use the Map function to enter the lat/long of the report by clicking on the map if he has a device with a GPS, and if not, he will be able to use Google Maps to drop a pin where the location is, or enter in the address of the report.
User can upload Photo, location, comments, title, report type of the report. (IOS6 FILE UPLOAD)
The user will be able (if he creates a user profile) to list/edit/update/delete reports in a table that he has reported. They will be color coded based on the status of each report. If the status of a report is changed by the department user, the reporting user will be notified by email if he so wishes.
This site is a way for the public to post reports about things that matter in a city. There will be many cities, and each city will have many departments, Each department will have a report type associated with it.
This seems like an interesting website. I am build it with good quality and as per your expectations. We can discuss more features once the basic required infrastructure is in place.