I have an application that allows users to edit certain parts of text and then email that out. My question is what is the best way to store this in a Microsoft SQL Server database. Right now I have two tables, one holding the HTML data and one holding the plain text data. When the user saves the info, it replaces newlines with br's and puts it in the HTML-conntaining table and then puts the regular text in the other table. This way the text box has the newlines when they go to edit, but the table that contains the HTML data, has the BR's. This seems like a silly way to do things. What would be the best practice? Thanks.
HTML – Best Practices for Storing HTML from Text Fields in a Database
htmlsql server
Related Solutions
For OLAP databases, normalization is often not the best approach - this is completely different from classical OLTP databases. The structure of your tables should be optimized for the queries you are going to run. I recommend the Wikipedia articles about star schema or snowflake schema, those are patterns for a good OLAP database design.
Here is a book about the topic I can recommend:
http://www.amazon.com/The-Data-Warehouse-Toolkit-Dimensional/dp/0471153370
Something you did not write (but really ask yourself) is why you actually want to restructure the system. Just because it is denormalized and you think this is not "best practice"? Or do you suffer from real performance or storage problems? If it is only the first reason, you should first read something more about good OLAP db design before changing the system.
Do you not have a server-side component running now?
I would avoid having the SQL Server send the Email itself.
If you just need code somewhere to wake up occasionally and check for specific conditions and take action, you could write a simple console app and use the Windows task scheduler on the server to run it every so often. If the conditions are right, send your Email messages, otherwise, just exit clean. Check again the next time the task scheduler fires up your console app.
Or you could write a Windows service to essentially do the same thing, except that you would use the task API or Windows timers to occasionally wake up and check for appropriate conditions. The service will be a bit trickier than scheduling a simple console app. There are service installation issues. You can't have your worker code blocking the primary thread where you're listening for important things like service control messages. Your worker code needs to be responsive to these events, as well. So if you get a shutdown message, you need to respond to it in a reasonable amount of time (a second or two, not a minute or two). You probably want to consider some kind of a queue-based design that can send half the messages, get shut down, wake back up and send the other half without losing it's place, and so on.
If you're not savvy about issues like service installation and the thread synchronization and service control events, I'd just do the simple console app and use the Windows Task Scheduler. "Low tech" can actually be kind of a beautiful thing.
But to reiterate, I definitely would not do this directly from SQL Server. Just because you can (.NET extended stored procedures) doesn't mean you should. :-)
Best Answer
It's not silly at all.
Consider the following possibilities:
Store just HTML. That 's silly! Once stored this way, editing it would be painful: you either should decode it, or simply forbid any edition or force users to write HTML.
Store just text. It may work. Until, maybe, you find that converting text to HTML is the bottleneck¹ which slows your application down. For small scale apps, this approach is still ok and probably the easiest one.
Store text and HTML. That's what you've chosen, and it has the benefits of both previous approaches: editing content is simple, and at the same time you don't slow the application down by doing the conversion every time the page is generated.
If there is a thing which is annoying, it's the fact that you are using two tables. Why not keeping this data in a single table, with one column for original text, and another column for HTML?
¹ Remember one rule: don't guess what is slowing the application down: use a profiler. Discussing which approach is faster is good for an informal talk with your friends, but not a good approach to develop a scalable application without doing unnecessary work. My example of saving HTML vs. generating it on the fly, for example, is only good in theory. In practice, (1) you'll cache the results anyway, and (2) maybe, who knows, loading the data from the database may be much slower than generating HTML.