Database: Does the appendix 'HX' have significant value?

Question

I'm working with a legacy application with surprise surprise, next to no useful documentation on naming convensions or over all data structure.

There are similarly named tables in the database with one of them ending in HX. Does this hold any significance in anyones database experience?

The data seems to be replicated so I would assume that it is a Historical table of sorts, but I just want to be sure before I avoid populating it.

Thanks in Advance.

Cory

Answer 1

I've seen this before but I don't think it's usage was part of any standard. When I saw it was was used to prefix tables (hx_ReportingUsage) that stored information on historical index usage. Something similar to what this article talks about.

Again, I think this was just an internal naming convention. Your best bet would probably be to search all stored procs, UDFs and code you have access to for the table name and see if you can piece together how it's being used.

If you are using SQL Server you can use this query to look for text in stored procs:

SELECT OBJECT_NAME(id) 
    FROM syscomments 
    WHERE [text] LIKE '%foobar%' 
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
    GROUP BY OBJECT_NAME(id)

Answer 2

In the few places I've come across this it was related to history tables. It has depended on the system. I've seen this mostly in the Oil & Gas world, not so much outside that industry.

I shouldn't just assume that's the case though. If it's possible, I'd do a dependency search to find out if there are any scripts dependent upon the table - if they're history tables, you'll likely find a copy procedure or trigger somewhere to keep them populated.

Check out sp_depends to see if that can shed any light on the subject.

Exec sp_depends 'table_name_hx'

You should get a list of everything that's got references to it and what type of object is referencing.

Answer 3

It might have come from the medical field. They often have abbreviations like Rx for prescription, Sx for symptoms, etc. Hx is medical history: http://en.wikipedia.org/wiki/Medical_history. This could have found its way into other fields and even databases.