0

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!

  • 1
    Possible duplicate of [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – philipxy Jun 28 '19 at 03:15
  • 1
    "they don't want to enter data into two fields" What possible justification comes with that? PS (Obviously--) This is a faq. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. – philipxy Jun 28 '19 at 03:21

1 Answers1

1

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
Cheddar
  • 530
  • 4
  • 30