DataBase Format

[Robert Santorelli]

To be compliant with HUD does the HMIS Homeless Management Information
System need to be written with a csv or xml db or does it only need to
export to those formats?

In other words can PHP / MySQL work if it exports in csv?

[David Talbot]

To be HMIS compliant you don't have to support any particular export format
at all, just be able to produce a valid APR report and gather the required
data.

To be a *GOOD* HMIS vendor, it's strongly recommended you support HUD XML
and/or CSV. Customers need a way to get data out of your system to integrate
with other systems or to do other random stuff. It's best if you can adhere
to the standards provided but it is not a hard requirement to be an HMIS
software vendor.

David Talbot, Vice President Technology
Data Systems International
www.data-systems.com
Office    888-449-6328
Cell 254-723-0082
Fax       866-224-4506

[Cy Welch]

In addition to what David said, not only is an HMIS not required to store
data in CSV or XML, but is required to store data in a "binary" format.
According to the Federal Register databases such as Access are specifically
mentioned which would clearly say that mysql is fine.

Cy Welch
Senior Programmer/Analyst
MetSYS Inc
http://www.metsysinc.com

-----Original Message-----
From: HMIS_Data_...@googlegroups.com
[mailto:HMIS_Data_...@googlegroups.com] On Behalf Of Robert
Santorelli
Sent: Monday, March 12, 2007 2:03 PM
To: HMIS_Data_Integration
Subject: [HMIS Data Integration] DataBase Format

[Brian Sokol]

Everything David and Cy said is true. PHP/MySql is fine

Except: The data standards (section 5.1.7, pasted below) do say quite
clearly that the HMIS must be able to export any and all day to a CSV.
It doesn't specify a particular CSV format, just CSV in general. That
said, we of course stongly encourage HMIS CSV. XML is not
mentioned in the Standards, but is of course strongly encouraged as well.

In sum:
Required: Store in binary. Export to CSV.
Strongly Encouraged: Use of HMIS CSV and/or HMIS XML

*************

5.1.7. Data Export
Although a standard environment is not specified, any HMIS application must
be capable of exporting any and all
data collected into a comma-separated values text file using the following
format:

--All fields in a given record are separated by a comma;
--All records within a given text file contain the same fields;
-- Blank fields are signified by the comma ending the previous field (or
the beginning of the line if the field is the first in the record) followed
by a comma indicating the end of the empty field;
-- Fields containing text information (as opposed to numeric) will be
surrounded by double quotes whenever the field includes blank spaces,
commas, or other symbols not part of the standard alphabet;
--The first line of the file shall be a list of the field names included
in every record in the file; and
-- The list of field names shall be in the same format described above.

**************************

Brian Sokol
Abt Associates Inc.
Phone: 617-349-2532
Fax: 617-349-2670
http://www.abtassociates.com

"Cy Welch"
<cyw...@yahoo.co
m> To
Sent by: <HMIS_Data_Integration@googlegroups
HMIS_Data_Integra .com>
tion@googlegroups cc
.com
Subject
[HMIS Data Integration] Re:
03/12/2007 10:05 DataBase Format
PM


Please respond to
HMIS_Data_Integra
tion@googlegroups
.com

-----------------------------------------
This message may contain privileged and confidential information intended
solely for the addressee. Please do not read, disseminate or copy it unless
you are the intended recipient. If this message has been received in
error, we kindly ask that you notify the sender immediately by return email
and delete all copies of the message from your system. Thank you.

[Eric Jahn]

On Mon, 2007-03-12 at 22:05 -0400, Cy Welch wrote:
> In addition to what David said, not only is an HMIS not required to store
> data in CSV or XML, but is required to store data in a "binary" format.
> According to the Federal Register databases such as Access are specifically
> mentioned which would clearly say that mysql is fine.

The "binary" part I thought interesting, since, on disk, a text file is
binary.

So, perhaps you could write your database out as a csv with one record
per line, and prefix it with a binary-encoded version number for the
schema revision.

You could still encrypt your text file, so this isn't a security
issue...and not an trivial point since it ma make more sense, in some
applications, to put certain HMIS data in text files for
portability/ease of maintenance/accessibility, simplicity, etc...

--
Eric Jahn
Alexandria Consulting LLC
3126 8th Ave. N
St. Petersburg, FL 33713
941.321.1466

[William Matson]

Yikes, Eric - a flat file. Don't give anybody any ideas. We've had to deal with a couple of those systems, and it is not fun.

Bill

Sent via BlackBerry from T-Mobile

[David Talbot]

Hey! Our system used to work that way! (of course it was 1985 at the time
and commercial databases cost more than most non-profits operating budgets
for a year)

Sadly, we've done conversions from ancient home grown systems that worked
off of flat files as recently as a few months ago, so indeed, sadly systems
like that do indeed still exist and will continue exist until "the guy" that
maintains it dies or retires. :/

David Talbot, Vice President Technology
Data Systems International
www.data-systems.com
Office    888-449-6328
Cell 254-723-0082
Fax       866-224-4506

[Eric Jahn]

On Tue, 2007-03-13 at 17:11 +0000, William Matson wrote:
> Yikes, Eric - a flat file. Don't give anybody any ideas. We've had to deal with a couple of those systems, and it is not fun.

Just saying it's a legitimate option, which, in some limited resource
applications may be the best call. Plus, paper is still the reality in
many places, and I'd rather work with any type of electronic data than
paper. -Eric

[Brian Sokol]

Yes but the question here was on the legitimacy of PHP/MySql which I think
we would all agree is preferred to flat files, despite the retro flair.

Though, to be fair, we did have to backpedal to CSV after the XML was done,
so who knows. I hear punch cards are all the rage these days.

Brian Sokol
Abt Associates Inc.
Phone: 617-349-2532
Fax: 617-349-2670
http://www.abtassociates.com

Eric Jahn
<eric@alexandriac
onsulting.com> To
Sent by: HMIS_Data_Integration@googlegroups.
HMIS_Data_Integra com
tion@googlegroups cc
.com

Subject
[HMIS Data Integration] Re:

03/13/2007 12:35 DataBase Format

PM


Please respond to
HMIS_Data_Integra
tion@googlegroups
.com

(See attached file: signature.asc)

[Eric Jahn]

On Tue, 2007-03-13 at 12:56 -0400, Brian Sokol wrote:
> Yes but the question here was on the legitimacy of PHP/MySql which I think

> we would all agree is preferred to flat files, despite the retro flair. ...

Yes, that was the origin of the discussion, but in exploring the rules,
"binary format" became criteria. Actually, I don't *always* think an
rdbms is the way to go. Sometimes, filesystem-based storage is
preferable, and not in a retro sense. I recently moved my software
repositories off an rdbms to flat files, as it was more stable and
portable. Also, there are many that would argue the flat file's
licensing terms are far superior to MySQL for corporate use (though
there are many other great free rdbms's out there). I'm not advocating
building an enterprise solution of flat files, but it may be an
appropriate/cheap/free storage methodology.

[David Talbot]

Good point. Our snazzy new offline capable version stores the data in
encrypted XML (given that it's crypted, it's de-asciied (probably what they
meant when saying "binary") but is kinda like being in a text format) while
its offline but the data does go into the RDB once it synchs.

I assume your text files end up in an RDB eventually right? Would be *very*
hard to write reasonable reports off of text files.

David Talbot, Vice President Technology
Data Systems International
www.data-systems.com
Office    888-449-6328
Cell 254-723-0082
Fax       866-224-4506

[William Matson]

A punch card pilot. That would be fun.

Sent via BlackBerry from T-Mobile

-----Original Message-----

[Eric Jahn]

On Tue, 2007-03-13 at 12:37 -0500, David Talbot wrote:
> Good point. Our snazzy new offline capable version stores the data in
> encrypted XML (given that it's crypted, it's de-asciied (probably what they
> meant when saying "binary") but is kinda like being in a text format) while
> its offline but the data does go into the RDB once it synchs.

David,
Yes, I was thinking about your app when I wrote that. Also about all
the new Web 2.0 apps out there storing stuff on the client and
transmitting XML or JSON or what-not, and for which we can't expect to
install a different rdb for every web site.

> I assume your text files end up in an RDB eventually right? Would be *very*
> hard to write reasonable reports off of text files.

Yes, I'd still have the RDB be the central data store, for
efficiency-sake. And then we could muddle the discussion even more with
XML databases, but I've never had the time to play with them. -Eric

[Cy Welch]

And that's a good point Brian. The standard just says you must be able to
export data to CSV, which based on my years of programming experience, its
close to if not trivial for almost any database management system, whether
file based (which can still be an RDBS) or server based to output to.

Our experience so far has been absolute shock when a customer wants to send
data for inclusion without any custom work, and we point them to the XML and
CSV file definitions since we support both of those formats. Basically most
of those who want to send in data, tend to be working with a flat file and
don't want to spend the time to create files themselves, or for that matter
pay someone to do it for them. Add the that the fact that most of them are
collecting usually ONLY the data elements (the minimum) to generate an APR,
and they can't even provide all the data.

I have faith it will get better however, as I believe all the vendors (at
least the ones who have been around for a while) are capable of handling the
HUD XML and CSV formats either already, or with little ramp-up when demand
occurs.