1

i have a problem with designing db tables. for explaining my problem here is a simple forum db. the forum table contains following columns:

|  forum_id  |    title      |     desc     |   last_update    |
------------------------------------------------------------
|      1     |    'title'    |    'desc'    |    2011/5/3      |

now when i design this table should i allow null values for the title, desc and last_update columns or not??

if i think logically, a forum should always have value for title and desc columns while the last_update column could be null or not null but if the user tries to update the title column, he/she has to provide the desc value also. so this is not good since with this approach we have to find the desc value of the forum that we're editing and pass it again to the sql statement which will result to an unecessary overwrite of value that already exist!

problem 1: at the other hand, if i declare seperate sps for each of update statements(one for each column update) and my table is big enough then that would cause the db to become full of sps that basically do the same thing on the same table.

problem 2: if i allow null values then an update like this will compromise my data integrity:

update ForumDB(title, desc, last_update) 
values ('edited title', null, 2011/6/4)
where forum_id = 1

so what can be done to prevent both of this side effects??

Layke
  • 51,422
  • 11
  • 85
  • 111
jim
  • 95
  • 4

2 Answers2

3

I agree with Nivas it doesn't sound like these columns should be nullable as you should just enforce the rule that a forum cannot be created without these 2 mandatory values.

Regardless anyway from your problem 2 it sounds like you definitely want to disallow updates to null in any case.

So you could have a generic UPDATE procedure that accepts all parameters and interprets a NULL parameter as meaning leave the original value alone.

SET title = COALESCE(@title, title), 
    Desc= COALESCE(@Desc,Desc)

You would need to check if there are any performance implications of these non updating updates in your RDBMS. For SQL Server some information on this can be found here.

Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • thanks for the link. if you check the summary section on that page you'll understand that doing this null cheking plays a very important role in a large scale database. – jim Sep 01 '11 at 18:01
3

A database is designed to hold data, the is the only purpose of the database (and is by no means a small task). And data is one of the most (if not the most) important parts of your application.

So you do something (anything) that will not compromise the integrity of your data.

How you access, how you update are relatively less important than data integrity. They can always be optimized. You cannot compromise on data integrity. (If you want some pointers on how to optimize, you have to specify the technology you are using and the approach you are following/planning to follow)

So: You do not allow null values for the title and the desc columns.

Problem 2 is actually not a problem at all: The size of the stored procedure (If that is what you meant by SP) to update the database is not dependent on the volume of the data. Even if you have a huge table, you still have only one (or a bunch) of stored procedures to manipulate the data.

Problem 2 can be solved by multiple approaches, depending on the technology you are using. Even if you have a hundred columns, your application will not have hundred different actions, each for a column. Columns are logically grouped, you update a set of them per update. You will definitely not need a separate procedure for each column.

Your application architecture and design matters here. Let us take a simple scenario: A UI where you allow the user to view the name and description of a forum and to update them both.

You are worried that the user may want to update only the title and for this you have to fetch the latest description and update them both.

But why?

You will have to fetch them both anyway. To display them. You can use the same values to update. All you have to make sure is that someone else has not already updated the column and you are overwriting them. You can use the modified_by column to check whether the data you have is the latest or not. (One of the reasons why this column should hold the time in addition to the date).

See Should you enforce constraints at the database level as well as the application level? for a discussion on a similar topic.

Community
  • 1
  • 1
Nivas
  • 18,126
  • 4
  • 62
  • 76
  • When he refers to his table being big, I think he means lots of columns and not the volume of data. In that case, using a different SP for each column (or combination of columns?!) would require more SPs. – Tom H Sep 01 '11 at 12:20
  • thank you for that modified_by idea. it's so cool however if you're using sql server like me then you just have to mark a ckeck box named "optimistic concurancy" when adding the db to your app. it will do just like your modified_by idea. – jim Sep 01 '11 at 18:04
  • optimistic concurrency sounds cool. Will read on that. Could be there are similar options in Oracle/MySql.. I am little out of date... – Nivas Sep 02 '11 at 03:15