departments or agencies? adding new ones?

[rkiddy]

The narrative text describes these as agencies. But the table column head says "Department". Which is this a list of?

You have both "agency" and "name" in the data.json file. Is this "department name" of the department that the "agency" is in?

It makes more sense to me that "agency" would be a name of an agency and that "department" would be the name of the department that the agency is in. And there should be both an "Agency" and "Department" column.

Does this make sense?

And I have an additional source of data.

You all have 106 agencies in here. There is a state site, the list of agencies of which reports are required by the legislature (http://agencyreports.ca.gov/reportdue) and this list contains 392 agencies. I already have MySQL tables which contain this list and a table for the reports required of each agency. I can add these without adding duplicates. Reportees are usually the legislative committee that is asking for the report, or the Governor, or such as that. Let me me what might be best done here.

CREATE TABLE `agencies` (
  `pk` int NOT NULL,
  `id` int DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`pk`)
);

CREATE TABLE `reportee_joins` (
  `report_pk` int NOT NULL DEFAULT '0',
  `reportee_pk` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`report_pk`,`reportee_pk`)
);

CREATE TABLE `reportees` (
  `pk` int NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`pk`)
);

CREATE TABLE `reports` (
  `pk` int NOT NULL,
  `agency_pk` int DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `authority` varchar(255) DEFAULT NULL,
  `descrip` varchar(255) DEFAULT NULL,
  `duedatedesc` varchar(255) DEFAULT NULL,
  `duedate` char(10) DEFAULT NULL,
  PRIMARY KEY (`pk`)
);

select count(*) from agencies;
+----------+
| count(*) |
+----------+
|      392 |
+----------+

select count(*) from reportees;
+----------+
| count(*) |
+----------+
|      115 |
+----------+

select count(*) from reports;
+----------+
| count(*) |
+----------+
|     1823 |
+----------+

[roughani]

Sorry for the confusion. In the public administration world, "agency" is parlance for any public entity (e.g., school district, an individual school, a state board of education, a county office of education, etc.). On the other hand, state government has what are known as "control agencies" that are intermediaries between departments and the governor's office. Those agency officials comprise the governor's cabinet and are included in the data schema so that users can query by topic, not just individual department. For example, most people don't know that there are 16 departments, boards, and offices that all fall under the California Health and Human Services Agency. That's helpful to know if you're interested in the status of health and human services open data in general.

The list of agencies comes from the official state org chart. It's missing some state entities by virtue of what can fit on a single page. However, I think we've got 95% of them. I refer to the other 5% as budget dust, since they're likely inconsequential in the context of open data (and if your agency is running on budget dust, it's unlikely you'll ever have the capacity to publish data). The Legislature's list is on the opposite end of the spectrum, as it includes non-state entities such as counties and special districts. Because the Legislature writes the laws of California, they can require that any public entity issue any report that they want them to issue.

What we REALLY need is a public agency data schema that actually reflects the entire structure of government – from the tiniest cemetery district, up to the White House. We'll get there eventually, just not in this project.

[vykster]

@roughani for this project you're not linking to portals, structured, data, etc? Do you have a process to vet updated status for agencies.

Ex: The Department of Justice is listed as yes for a publisher of data, but not structured data or as having a portal. With the launch of Open Justice California, I would argue the CA DOJ now does have both published data and a portal but as of yet no APIs.

It would seem the next step for me would be changing the no on DOJ to corresponding yes/sorta and submit a pull request, but not submit any details on where the portal is or who I am or what data is there.

  {
    "name"        :  "Department of Justice",
    "agency"      :  "Constitutional Office",
    "url"         :  "http://oag.ca.gov/",
    "publish"     :  "yes",
    "structure"   :  "no",
    "portal"      :  "no",
    "api"         :  "no"
  },