Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    24

    Less Frequently Usage field --A Schema Design Question

    Hi,

    Here is my question:

    Shall I have a seqarated table for a less frequently usage field?

    There are a set of five, six less frequently usage fields on a current table. Most of time (over 80%), they are null. Is a better practice to have a set of tables for those fields?

    Thanks for your advice.

    v.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Less Frequently Usage field --A Schema Design Question

    It may be more relationally "correct" to separate them, but there may be no need to do so in practice. In Oracle, for example, such columns should be placed at the end of the row; then when they are null they occupy NO space. This will be more efficient than having to join to another table to get them.

  3. #3
    Join Date
    Jul 2003
    Posts
    24

    Re: Less Frequently Usage field --A Schema Design Question

    Originally posted by andrewst
    It may be more relationally "correct" to separate them, but there may be no need to do so in practice. In Oracle, for example, such columns should be placed at the end of the row; then when they are null they occupy NO space. This will be more efficient than having to join to another table to get them.
    Thanks Andrew for your reply.

    I incline to be "more relationally 'correct'". I have some concerns/questions with the approach.

    1. Insertion operation:

    Do I need a set of query statements for each separated table? (A condition of whether a valid data exists checking first, of course)

    2. Updating operation:

    Is the correct that a pair of delection and insertion operations is needed for the separated tables?

    3. Selection operation:

    The query is something like the followings:

    select ... from main_table left out join separated_table1 on ... left out join separated_table2 on ... left out join separaent_table3 on ...


    Thanks again.

    v.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Less Frequently Usage field --A Schema Design Question

    Yes, you would have to do all that. Probably a view based on the query in your point 3 would be useful. In Oracle at least, you could then also define an INSTEAD OF trigger on the view so that it can be used to insert, update and delete data using a single DML statement.

  5. #5
    Join Date
    Jul 2003
    Posts
    24
    Thanks Andrew again for your advice.

    Yes, I could use a View for the selection operation. Since the programming environment is Java, a pre-definited query string in JDBC can serve the same functionality of a View in DB.

    v.

Posting Permissions

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