Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    5

    Unanswered: Database design issue

    Hello folks,

    We are developing a datamart to which data comes from different sources (SQL, Sybase, Excel, MDB). There is going to be a refresh process that will do the retrieval.
    In the source tables, there are columns that can be nullable.
    In the destinaton tables, we are planning to convert all nullable to NOT Null, so that the indexing can be applied and the retrieval will be faster.

    But then what default value can we give it for data types Varchar, Numeric and Date.

    Is it fine to give Spaces(1) for Varchar and zero for numneric. What do we give for date?

    If anyone can give an insight into these questions, i really appreciate it.

    Thank you,
    Venugopal

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    You can give '' for the varchar.

    Be very careful though as you will be losing information as there will now be no difference between a null and empty string or null and zero numeric. These differences may be meaningful in the source which will now be lost.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can index a column that allows NULL values. Only the primary key is not allowed to take NULL values.

    blindman

  4. #4
    Join Date
    Oct 2003
    Posts
    706

    Re: Database design issue


    In the destinaton tables, we are planning to convert all nullable to NOT Null, so that the indexing can be applied and the retrieval will be faster.
    Eh?

    Columns that contain NULLs can certainly be indexed; they just can't be a primary key. There is no speed issue involved.

    The database schema must reflect the requirements of your data. If the original data can contain NULL values, then the repository database must do so also. There is no value whatsoever that you can substitute for "the absence of any value," which is what NULL is.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Database design issue

    And it can't be a unique index....DB2 has an option that allows it, but It doesn'r seems so with SQL Server...unless I'm missing it (WHAT? AGAIN?)

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99 (Col1 int, col2 char(10))
    GO
    
    CREATE INDEX myIndex1 ON myTable99 (Col1) 
    GO
    
    
    INSERT INTO myTable99(Col1, Col2)
    SELECT 1, 'A' UNION ALL
    SELECT 2, 'B' UNION ALL
    SELECT Null, 'C' UNION ALL
    SELECT Null, 'D'
    
    
    SELECT * FROM myTable99
    GO
    
    
    CREATE UNIQUE INDEX myIndex2 ON myTable99 (Col2) 
    GO
    
    INSERT INTO myTable99(Col1, Col2)
    SELECT 1, 'E' UNION ALL
    SELECT 2, 'F' UNION ALL
    SELECT Null, Null
    GO
    
    SELECT * FROM myTable99
    GO
    
    INSERT INTO myTable99(Col1, Col2)
    SELECT 3, Null
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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