How best to represent rational numbers in SQL Server?

Question

I'm working with data that is natively supplied as rational numbers. I have a slick generic C# class which beautifully represents this data in C# and allows conversion to many other forms. Unfortunately, when I turn around and want to store this in SQL, I've got a couple solutions in mind but none of them are very satisfying.

Here is an example. I have the raw value 2/3 which my new Rational<int>(2, 3) easily handles in C#. The options I've thought of for storing this in the database are as follows:

1. Just as a decimal/floating point, i.e. value = 0.66666667 of various precisions and exactness. Pros: this allows me to query the data, e.g. find values < 1. Cons: it has a loss of exactness and it is ugly when I go to display this simple value back in the UI.

2. Store as two exact integer fields, e.g. numerator = 2, denominator = 3 of various precisions and exactness. Pros: This allows me to precisely represent the original value and display it in its simplest form later. Cons: I now have two fields to represent this value and querying is now complicated/less efficient as every query must perform the arithmetic, e.g. find numerator / denominator < 1.

3. Serialize as string data, i.e. "2/3". I would be able to know the max string length and have a varchar that could hold this. Pros: I'm back to one field but with an exact representation. Cons: querying is pretty much busted and pay a serialization cost.

4. A combination of #1 & #2. Pros: easily/efficiently query for ranges of values, and have precise values in the UI. Cons: three fields (!?!) to hold one piece of data, must keep multiple representations in sync which breaks D.R.Y.

5. A combination of #1 & #3. Pros: easily/efficiently query for ranges of values, and have precise values in the UI. Cons: back down to two fields to hold one piece data, must keep multiple representations in sync which breaks D.R.Y., and must pay extra serialization costs.

Does anyone have another out-of-the-box solution which is better than these? Are there other things I'm not considering? Is there a relatively easy way to do this in SQL that I'm just unaware of?

Answer 1

If you're using SQL Server 2005 or 2008, you have the option to define your own CLR data types:

Beginning with SQL Server 2005, you can use user-defined types (UDTs) to extend the scalar type system of the server, enabling storage of CLR objects in a SQL Server database. UDTs can contain multiple elements and can have behaviors, differentiating them from the traditional alias data types which consist of a single SQL Server system data type.

Because UDTs are accessed by the system as a whole, their use for complex data types may negatively impact performance. Complex data is generally best modeled using traditional rows and tables. UDTs in SQL Server are well suited to the following:

• Date, time, currency, and extended numeric types
• Geospatial applications
• Encoded or encrypted data

If you can live with the limitations, I can't imagine a better way to map data you're already capturing in a custom class.