Home » SQL & PL/SQL » SQL & PL/SQL » table design question: storing both character data and numeric data in a VARCHAR2(10)
icon5.gif  table design question: storing both character data and numeric data in a VARCHAR2(10) [message #241481] Tue, 29 May 2007 15:05 Go to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
We have a data architect who is suggesting that the datatype of a column be VARCHAR2(10) so it can contain both character data and numeric data. Besides the fact that
- Oracle will do an implicit conversion when doing math on the column for the records that have numbers in the column,
- and the fact that an explicit conversion may sometimes need to be written into the SQL code
- and the fact that a door is open to load corrupted data (i.e. numeric data in an input file that is corrupted)
what would be some other reasons to only store numeric data in columns that have numeric datatypes?

Thanks.
Re: table design question: storing both character data and numeric data in a VARCHAR2(10) [message #241483 is a reply to message #241481] Tue, 29 May 2007 15:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Logic.
Number is for numeric.
Varchar is for string.
Date is for date.
Timestamp is for timestamp.
...

What would be the reason to store data in wrong datatype?
Beside you want a corruption that WILL (and not could or may) happen?

I have an idea. Why not stored all data in a BLOB? This can contain any data whatever is the type and size.

I forgot: fire your architect.

Regards
Michel

[Updated on: Tue, 29 May 2007 15:14]

Report message to a moderator

Re: table design question: storing both character data and numeric data in a VARCHAR2(10) [message #241530 is a reply to message #241483] Tue, 29 May 2007 21:58 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There's always an exception that proves the rule:

Say you're designing a data warehouse. The object is to design an Enterprise Data Model into which data from any source system may be transformed.

Clearly, currency and quantities get stored in NUMBER datatypes, but what about natural keys (ie. the primary key from the source system - NOT the primary key of the DW table). We may have just a single source that has a numeric natural key, but what if we takeover another company and integrate their data? They may have character natural keys!

I see a compelling argument to make all natural keys in a data warehouse VARCHAR2 so that they can adapt to changing interface requirements.

Clearly this argument does not extend to any column that will be used in arithmetic. Nor does it detract from Michel's sentiments, which I agree with in all other respects.

Ross Leishman
Re: table design question: storing both character data and numeric data in a VARCHAR2(10) [message #241546 is a reply to message #241530] Tue, 29 May 2007 23:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course, I was talking about data on which you applied operations, functions or not equality condition (+, *, %, ratio, ||, between...)
If you don't do anything with them but selecting them or on them (equality) then datatype is not important and all is in the end strings or even binary data.

Regards
Michel
Re: table design question: storing both character data and numeric data in a VARCHAR2(10) [message #241731 is a reply to message #241481] Wed, 30 May 2007 09:03 Go to previous message
gintsp
Messages: 118
Registered: February 2007
Senior Member
1) If you have character column where numbers must be - is is only matter of time when you'll get charecters there.
2) You'll get more CPU usage for the conversion.
3) You'll get possibility to get incorrect results comparing values:
SQL> select case when 2 < 10 then 'yes' else 'no' end from dual;

CAS
---
yes

SQL> select case when '2' < '10' then 'yes' else 'no' end from dual;

CAS
---
no

4) You'll get possibility wrongly ordered rows because of the same reason.
5) You'll get more nasty not easily to find out bugs because of the 3) and 4).
6) your overall rating of data waste vs data base will increase.

Gints Plivna
http://www.gplivna.eu
Previous Topic: It is possible?
Next Topic: How to integrate SUM in WHERE?
Goto Forum:
  


Current Time: Sun Dec 04 19:08:34 CST 2016

Total time taken to generate the page: 0.08954 seconds