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.

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.

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.

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.