0

If you want to create something like a type driven selection with values :

enter image description here

What is the best way to save this object-wise in Java (to later persist this with jpa)

I would also like later on to query over this to get e.g. the avgerage score (what to do with the NA value is also something to think about).

No idea how to tackle this, so suggestions would be more then welcome.

Matt
  • 74,352
  • 26
  • 153
  • 180
Darth Blue Ray
  • 9,445
  • 10
  • 35
  • 48
  • Not sure the term validation list is applicable, but this looks like a simple type driven selection with values ranging from 0 to 10. A null in the associated DB column would map to N/A on the screen. – Perception Jan 07 '13 at 13:06

2 Answers2

2

If your values are only ever going to be numeric plus N/A, then you can do as Perception and Gavin Xiong suggested and store this as a NUMBER column in the DB. I'd make the column nullable and use DBNULL to represent N/A as Perception suggests, rather than using a magic value (even a "standard" one like -1). Neither of them addressed how to represent this in the Java code, but I think that null in Java is a more natural mapping to DBNULL in a database in most cases, so I'd represent this as an Integer (Object) rather than an int (primitive) with null == DBNULL == N/A, unless there's a reason to do otherwise.

You yourself brought up the edge case of how to handle formulas (e.g. AVERAGE) for N/A values, and you'll need to define your desired behavior in those cases to get a final answer. Your decision of how to store these value will also make a difference to how complicated your formula queries have to be; if you store the value as -1 the way Gavin suggests, you're definitely going to have to write a query with a WHERE clause to exclude those rows from an average value (if that's your desired behavior), whereas you might get some of that for free if you make the column NULLABLE and store DBNULL as the value for N/A.

One note: you should be pretty sure that you're not going to have any non-numeric values other than N/A before you map N/A to DBNULL. If you think there's a good chance there will be other non-numeric values in the future, I'd think seriously about storing this differently, because DBNULL is only ever going to let you represent one non-numeric value. If you have to go down that path, I'd store everything as VARCHARs in the DB, and then add code in either Java or SQL/JPA that parse the numeric strings into a number and runs your formula on them dynamically. (You could do it with multiple magic values like -1, -2, etc., but it will quickly get kludgy if you have more than a couple of them.)

Tim
  • 2,027
  • 15
  • 24
  • In this case the "feedback" will not change so the given idea would do nicely. thx – Darth Blue Ray Jan 07 '13 at 13:51
  • The reason why I suggest map N/A as -1 is a performance concern. I don't know what's DB you are working on, but it's something you should definitely take into consideration. You can find similar suggestions here: http://stackoverflow.com/questions/1017239/how-do-null-values-affect-performance-in-a-database-search – Gavin Xiong Jan 07 '13 at 14:21
  • In this case MS SQL Server so normally I will not have issues with an index – Darth Blue Ray Jan 07 '13 at 14:27
  • Gavin Xiong's point about the tradeoffs of NULL vs. a "magic" value is a good one; a database query with a condition containing MYCOLUMN IS NOT NULL will be slower than the same query with MYCOLUMN <> -1. Whether that difference in performance will be meaningful for your application, and whether getting that performance improvement is worth having to code up more-complex queries, depends on your specific situation. (And it will probably be more impacted by the volume of data you'll be storing and the frequency and complexity of your queries than by the particular DBMS engine you're using...) – Tim Jan 24 '13 at 13:32
1

Why not take it as a radio box and map it into a int property of an object. The value range from 0 to 10 and NA can be represented as -1.

Gavin Xiong
  • 957
  • 6
  • 9