CODEWITHSUNDEEP
sql-server-2008relational-database
user11208669
user11208669

Reputation:

Best practice for storing numeric ranges

We have an ERP system which feeds into a SQL server database. We have a new product with a new data category and I need to create a field in our ERP system to accommodate a range of numbers. Color Temperature is the example I am using here:

The range would be 3000k-6500k

My team wants to put these numbers in one field because they don't want to enter data into two fields. I feel like we should have 2 fields min and max. What would be the best practice here from a database perspective? Populating the data into one field or splitting up the range into 2 fields?

Thanks!

Upvotes: 0

Views: 812

Answers (1)

Cheddar
Cheddar

Reputation: 530

You should do 2 fields. Something like colorRangeLow and ColorRangeHigh. I would even drop the K and name the field column with units (K). That way you can save the field as an integer and not an nvarchar. You could also use a link table if there aren't many ranges. For example if all the new products have a color range that would be 1 out of 5 total possible ranges then have a table with all the ranges and a tinyint ID field, then save that corresponding tinyint into the table instead. From there you can join on the look up table and get your data.

Example 1, low and high range

 Table A
 ColorRangeLow(K)      ColorRangeHigh(K) 
 3000                  6500
 2800                  4200

Example 2, look up table

Table A (Lookup table)
Range        ID
3000-6500    1
2500-8400    2
6000-8400    3
and so on for all possible ranges

Table B (Product Table)
Product      RangeLookUp
A            2
B            3
C            2

SQL code example
SELECT * FROM TableA LEFT JOIN TableB ON TableB.RangeLookUp = TableA.ID

Upvotes: 1

Related Questions