MySQL: Alternate solution of SQL Server's HierarchyId datatype

Question

My current application was built up in SQL Server 2008 server in JAVA with Hibernate and I had used HierarchyId data type for department hierarchy in my database.

I had written SQL queries to deal with HierarchyId datatype. And I also have n-Level of department tree structure.

Now I want to change my Database server from SQL Server 2008 to MySQL as per business requirement.

After feasibility checking I came with the solution that my whole application will migrate to MySQL database server except HierarchyId data type.

So my main challenge is to find alternate solution of HierarchyId data type with the minimal change in coding.

What is the best way to implement department hierarchy in my database?

Thanks...

Answer 1

I faced the similar situation when our team decided to migrate from MS-SQL to MySQL. We resolved the issue using the following steps:

1. Added a column of type varchar(100) to the same table in MS SQL.
2. Converted the hierarchyid from hexadecimal value to string using the hierarchyid.ToString() function as saved it in the newly created column using computed column functionality. for eg. 0x58 -> "/1/", 0x7CE0 -> "/3/7/".
3. The level of the entity is equal to no-of '/''s minus 1.
4. These columns could be migrated to the MySQL.
5. The IsDesendantOf() and is method was replaced with LIKE function of string concaenated with '%'.

Thus we got rid of the hierarchyid functionality in MySQL.

Whenever we face such an issue, we just need to ask ourselves, what would we have done if this functionality would not have been provided by the tool we use. We generally end up getting the answer optimally.

Answer 2

Mysql has no equivalent that I'm aware of, but you could store the same data in a varchar.

For operations involving the HierarchyId, you're probably going to have to implement them yourself, probably as either user defined functions or stored procedures.

What sqlserver does looks like the "materialized path" method of storing a hierarchy. One example of that in mysql can be seen at http://www.cloudconnected.fr/2009/05/26/trees-in-sql-an-approach-based-on-materialized-paths-and-normalization-for-mysql/