Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    Question Unanswered: table design question: storing both character data and numeric data in a VARCHAR2(10)

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool



    You are right to be concerned!

    Maybe what needs to be changed is that ignorant data architect!

    Data type-ing is one of a means to introduce some integrity in the database.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You gave too few information about the issue. Of course that you'll be able to store both numbers and characters into the VARCHAR2 column ... that's understandable. But, what information are you going to store in there?

    There *must* be a reason why he/she suggested you to set this column's data type to a character. Data type should define the nature of information stored into the column, as well as its maximum length.

    I just hope that he/she will not suggest you to store dates into a character column ...

  4. #4
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58
    The table will contain hourly data about electric power plants
    - generation MWh, Capacity MWh ... these are example numeric data
    - status information like "On", "Off", "Open", "Closed" ... these are example character data.
    - no date data will be stored in this varchar2 column.

    There is a data_type_code column (varchar2) that will contain data like "Output", "Capacity", "Status" ... etc.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If there are an unlimited number of data types, then you might be forced into doing this. However I would do it as follows

    create my_table
    (data_type_code varchar2(80),
    character_data varchar2(4000),
    numeric_data number,
    date_data date);

    This is not the entire table and is an example only. This you could store the value in it's correct type. If the number of data types is set, then have a column of the approporate type for each data type.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Electric power plants? Well, why didn't your analyst suggest to create a table with one large (BLOB?) column so that you could put ANYTHING into it, even this power plant's photo along with its chief and his secretary?

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    This discussion reminds me this thread on AskTom. Suppose that the column containing both character and numeric data is the result of this "flexible" design.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •