Home » SQL & PL/SQL » SQL & PL/SQL » What should the column datatype be? (Oracle 9i, windows, solaris, jdbc)
What should the column datatype be? [message #358532] Tue, 11 November 2008 17:27 Go to next message
gary.p
Messages: 3
Registered: November 2008
Junior Member
Hello,

I'm writing a build sql file for a database and I'm running into an issue with a column data type. The possible types and range of numbers being stored for a "current value" column are:

I8 (-128, 127),
I16 (-32768, 32767),
I32 (-2147483648, 2147483647),
U8 (0, 255),
U16 (0, 65535),
U32 (0, 4294967295L),
F32 (-3.4E+38f, 3.4E+38f),
F64 (-1.8E+307d, 1.8E+307d),
BOOL (0,1);

What should the column type be? It can't be number because I have to store a 64 bit floating number, it can't be binary_double because this is in 9i; it will be a severe performance hit to store it as varchar2 and parse it in Java; if it is raw bytes can I do a comparison such as, RAM.current_value <> EEPROM.current_value ?

I believe the table is normalized and I don't want to break this up into multiple columns/tables. Which would you choose? This particular table has the potential to grow to hundreds of millions of records by the way.

Thank you,
Gary


[Updated on: Tue, 11 November 2008 17:32]

Report message to a moderator

Re: What should the column datatype be? [message #358644 is a reply to message #358532] Wed, 12 November 2008 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Apart from SQL Reference, Chapter 2 Basic Elements of Oracle SQL, section Datatypes, what could we say more?
Pick up the ones that (best) fit your requirements.

Regards
Michel
Re: What should the column datatype be? [message #358703 is a reply to message #358532] Wed, 12 November 2008 04:19 Go to previous messageGo to next message
cookiemonster
Messages: 12405
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you're storing that many different types/ranges of numbers in the same column then I really don't see any way it can be meaningfully considered normalised.
Any datatype/size you pick for such a wide range is going to have downsides.
Re: What should the column datatype be? [message #358869 is a reply to message #358703] Wed, 12 November 2008 15:05 Go to previous messageGo to next message
gary.p
Messages: 3
Registered: November 2008
Junior Member
Thank you for everyone's input so far.

This may help visualize the scenario

Table "Parameter"
- Parameter_ID (PK)
- Name
- Max_Value
- Min_Value

Table "Values"
- Value_ID (PK)
- Parameter_ID (FK)
- Timestamp
- Current_Value

There is a 1 to Many relationship between the Parameter and Values table, respectively.

As far as I'm concerned, this is at least 3NF. If all of the parameters were of type Number, then this would be a perfectly acceptable table structure. There really shouldn't be a different "Values" table for each parameter datatype, right? In most cases you just choose a large enough column type to accept any of the possible inputs. In this case it ranges from bool to floating 64, hence my predicament.

We're really pushing for 11g in our production server so it looks as though I can use binary_double. I'm concerned about the overhead this may cause though.

With this further explanation, does anyone have an opinion of what they would do?

thanks again

EDIT: I found an Oracle sponsored powerpoint that states this:
BINARY_DOUBLE
More efficient than the NUMBER type
Hardware arithmetic/math are 5 – 10 times faster
Take up less space in memory/disk (5/9 vs. 1 – 22 Bytes each)
BINARY_DOUBLE has wider value range (e308 vs. e125)
No type conversion (use a byte-order neutral storage format)

looks like I am going binary_double and I will keep the structure as it is. If you think this is a hugely wrong idea, please let me know.

[Updated on: Wed, 12 November 2008 15:30]

Report message to a moderator

Re: What should the column datatype be? [message #358941 is a reply to message #358869] Thu, 13 November 2008 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
looks like I am going binary_double

This type does not exist in 9i as you said:
Quote:
it can't be binary_double because this is in 9i;


Regards
Michel
Re: What should the column datatype be? [message #358971 is a reply to message #358941] Thu, 13 November 2008 01:40 Go to previous messageGo to next message
gary.p
Messages: 3
Registered: November 2008
Junior Member
Currently it is 9i but after talking to my boss, it turns out he has been pushing for 11g for some time now and he told me to develop towards that version. I mentioned it in my 2nd post in this thread. Thanks Smile
Re: What should the column datatype be? [message #358989 is a reply to message #358532] Thu, 13 November 2008 03:16 Go to previous message
cookiemonster
Messages: 12405
Registered: September 2008
Location: Rainy Manchester
Senior Member
Question: what's the safest why to make sure a bool parameter is actually a bool.
Answer: store it in a NUMBER(1) with a check constraint to enusre it's 1 or 0.

I don't have a problem with parameter tables per se, it just seems to me that you're storing wildly unrelated parameters in the same table and you're going to lose out on some fundamental data checks (as illustrated by my question) for the sake of avoiding adding a couple of extra tables.
Previous Topic: alphanumeric
Next Topic: date format picture ends before converting entire input string
Goto Forum:
  


Current Time: Tue Dec 06 02:36:33 CST 2016

Total time taken to generate the page: 0.12060 seconds