March 24, 2004 at 3:15 pm
Greetings guys!
Is there an article or some consensus somewhere about common column names such as:
LastName, FirstName, MiddleName, Address1 or Street1, City, State, Zip, etc. in terms of their field length, type and field name? I am not in the IT business but working with others in my field (medical) to try to standardize some of our data columns.
I don't think there is a database out there that doesn't start with some of these basics, so your thoughts are appreciated. I don't want to reinvent the wheel and these are the really easy fields compared to what we are getting ready to argue about.
Thanks in advance!!
SMK
March 24, 2004 at 3:38 pm
I know of nothing specific on Medical specifications. I did some checking around google and came up nill as well. I do know there are several groups trying to standardize XML file layouts for things like this but not sure where to find. If you could base yours on the XML (if there is one) it will make XML easier to implement. Many are easy some are harder if you want to decide on your own. For example use the char 2 abbreviation for state as these are standard (unless you need to use state field for some international code). Zip is standard (US) char 9 (first 5 are zip, next 4 the plus 4 sometimes seen) or create a field each and use INT for Zip, and smallint for PLUS4 with a constriant over each to limit range. Names I tend to see varchar(50) and address tends to be either varchar(100) or varchar(255). If I come across anything I will pass along but I do suggest looking for an XML standards group doing something with medical to try and match.
March 24, 2004 at 4:09 pm
Antares, thanks so much. I am tracing every lead I can find because as you probably are aware, the medical field lags every other business segment by at least 10 years in terms of intelligent use of technology.
As far as "state" is concerned, this has potential for international use, so I was advised to go with varchar 5 there (and also replace "state" with "pstate" or the like because "state" is a SQL reserved word). Is there some standard numeric code for states rather than the two digit abbreviation?
Is zip char 9 or varchar 9 since some will use only 5 digit codes?
I am reading about XML and struggling to get the picture. XML is the cross-platform communication layer/ language, right? If I have an SQL DB, it woud communicate with other DBs via XML over the net? (you're obviously not talking to an IT person here)
SMK
March 24, 2004 at 8:14 pm
As far as "state" is concerned, this has potential for international use, so I was advised to go with varchar 5 there (and also replace "state" with "pstate" or the like because "state" is a SQL reserved word). Is there some standard numeric code for states rather than the two digit abbreviation?
Nothing official I know of except the dialing digits set forth for international calling of which you can obtain a list from http://www.consumer.att.com/global/english/country_codes.html which you could use. This will simplify things by reusing information.
Is zip char 9 or varchar 9 since some will use only 5 digit codes?
Again here it is up to you to set your standard. I personally am a freak and try to minimize space usage so I have a column for ZIP which is VARCHAR(5) with a constraint of 0 to 99999 based on INT value and a ZIPPLUS which is VARCHAR(4) with a constraint of 0 to 9999 absed on smallint value. If ZIPPLUS is null I use ISNULL like this to handle, in my table if ZIPPLUS is sent as 0000 I set to null as I don't need.
ZIP + ISNULL('-' + ZIPPLUS,'')
This is for presenting my data so I have a bit more flexibility.
I am reading about XML and struggling to get the picture. XML is the cross-platform communication layer/ language, right? If I have an SQL DB, it woud communicate with other DBs via XML over the net? (you're obviously not talking to an IT person here)
Not really a language perse, it is a layout definition more than anything. It is a way of representing data and objects in a text format with specific rules and requirements that govern the basic layout, some will call it a laguage but there are few real langage requirements. The key to XML is the fact you can output data from one system in a defined format and another system that knows that definition can easily pick it up to real it out so it can present it as thou it was being read directly from the other system. Being simple it is very portable between platforms so there is no restrictions on who can read it. However as for talking to other DBs, SQL can talk to most all DB's with little issue as long as the proper drivers are in the right place, no xml needed but security wise and for simplicity of dealing with multiple systems XML offers the best route as you output to a predefined structure and they read it back based on the same rules.
I am by far not an XML Guru but I see it's merits and have been playing with it and some of the optional things to utilize it in a way I get maximum bennifit (so far I am long ways away) but if every industry plans to use for interexchanging data anyway working together to define the structure for their industry will have a major impact on the bennifit. That is why I suggest finding out if anyone has already started for Medical industry or work on setting one up yourself with others whcih I suggest contact someone who is on the XML standards originization about both. http://www.xml.org/
March 25, 2004 at 10:46 pm
smknox,
I use to work at a health care outcomes company, back in the mid nineties and XML was just in its infancy back then. Yet, they were already talking about it in terms of collection outcome measures for things like JCAHO accreditation programs required by healthcare institutions by the N.I.H.
Check out http://www.xml.gov for all the inside the US xml adoption going on.
Here's some quick hits against the latest NIH activities for medical articles. http://www.nlm.nih.gov/news/electronic_archiving.html
http://www.ncbi.nlm.nih.gov/IEB/ToolBox/XML/ncbixml.txt
http://www.nlm.nih.gov/mesh/xmlmesh.html
As for healthcare institutions there are kind of two sides to the problem the providers and the payers. The payers have had some standards within their institutions, but a lot crap applies at the state level and so they are bogged down with not only federal compliance (i.e. HIPPA, etc) but also states insurance comissioner rules. If you are talking about within a healthcare institution the Medical records are totally proprietary by Healthcare instituion parent companies. The rule of thumb is usually if they can't bill for it why do they need to measure or track it.
If you are trying to work with Clinical Patient Records, check out the current problem statem from this artical found at NIH
http://cris.cc.nih.gov/public/presentations/ruffin.pdf
Best regards,
Peter Evans (__PETER Peter_)
March 26, 2004 at 4:54 am
Thanks, Peter. I will check out these leads. I am specifically working on clinical patient records, a very different horse from the billing/payer mindset. The information we (docs) need to track patient issues and outcomes has little to nothing to do with billing. In fact, I discard most of the clinical billing information because it's far from accurate enough to make clinical decisions with.
SMK
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply