June 25, 2008 at 8:26 am
All right, this is a simple question of your opinion. However, I would like it anyway.
Yesterday we had a meeting with some external customers. They use our ASP.NET app to enter data (this is strictly a B2B type of web app). One of the fields they enter is a comment field. They mentioned that they wanted this field expanded so they could enter more text. We'll probably go to something like VARCHAR(MAX) in the table of a SQL Server database.
Anyway, after these external customers enter this data there is a process in-house in which someone reconciles the data into another SQL Server database. We want to move this data from the first SQL database to the second SQL database. Logically it can go under one of the tables in the second SQL database, however when I looked at how our external customers have used this comment field I found that they had used it roughly only 10% of the time. Good database design would suggest that I create a second table, related to the first table, linked by the primary key from the first table to the new child table. But boy, it seems overkill just to capture one field in this secondary table.
What do you think?
Kindest Regards, Rod Connect with me on LinkedIn.
June 25, 2008 at 8:32 am
Personally, I'd leave it in the existing table. If you move it to a second table, then you have to create an outer join to the secondary table to be able to retrieve the data when it exists, and I don't think you will really be saving your self al that much creating the secondary table for that one field.
😎
June 25, 2008 at 8:56 am
You could go either way. Normalizing your database is good, but can cost a lot of complexity. Balance the two.
That being said, comments are something that could be attached to more than one entity and it is often good to be able to attach multiple comments along with information about when the comment was made and who made it.
June 25, 2008 at 8:57 am
Go with the first approach of changing the size of datatype, but avoid using MAX it is a BLOB type equivalent to text in earlier versions.
so use it carefully as it can increase your database size if, there are too many updates/inserts to these columns.
Maninder
www.dbanation.com
June 25, 2008 at 11:56 am
Mani Singh (6/25/2008)
Go with the first approach of changing the size of datatype, but avoid using MAX it is a BLOB type equivalent to text in earlier versions.so use it carefully as it can increase your database size if, there are too many updates/inserts to these columns.
You've brought up a different issue, and that is my proposed use of varchar(max). We have used that in other tables, because sometimes our users will have to write lots of text (>8000 characters). In this particular case the external users may not need that much room, but I would like to know why I should "... avoid using MAX ... as it can increase your database size...". Is there something inheritly resource hungry of varchar(max) that cause them to significantly increase a database if the user performs many updates of a varchar(max) field?
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply