What is the best way to store a field that support

问题:

I have a database and I have a website front end. I have a field in my front end that is text now but I want it to support markdown. I am trying to figure out the right was to store in my database because I have various views that needs to be supported (PDF reports, web pages, excel files, etc)?

My concern is that since some of those views don't support HTML, I don't just want to have an HTML version of this field.

Should I store 2 copies (one text only and one HTML?), or should I store HTML and on the fly try to remove them HTML tags when I am rendering out to Excel for example?

I need to figure out correct format (or formats) to store in the database to be able to render both:

• HTML, and
• Regular text (with no markdown or HTML syntax)

Any suggestions would be appreciated as I don't want to go down the wrong path. My point is that I don't want to show any HTML tags or markdown syntax in my Excel output.

回答1:

Decide like this:

1. Store the original data (text with markdown).
2. Generate the derived data (HTML and plaintext) on the fly.
3. Measure the performance:
   • If it's acceptable, you're done, woohoo!
   • If not, cache the derived data.

Caching can be done in many ways... you can generate the derived data immediately, and store it in the database, or you can initially store NULLs and do the generation lazily (when and if it's needed). You can even cache it outside the database.

But whatever you do, make sure the cache is never "stale" - i.e. when the original data changes, the derived data in the cache must be re-generated or at least marked as "dirty" somehow. One way to do that is via triggers.

回答2:

You need to store your data in a canonical format. That is, in one true format within your database. It sounds like this format should be a text column that contains markdown. That answers the database-design part of your question.

Then, depending on what format you need to export, you should take the canonical format and convert it to the required output format. This might be just outputting the markdown text, or running it through some sort of parser to remove the markdown or convert it to HTML.

回答3:

Most everyone seems to be saying to just store the data as HTML in the database and then process it to turn it into plain text. In my opinion there are some downsides to that:

1. You will likely need application code to strip the HTML and extract the plain text. Imagine if you did this in SQL Server. What if you want to write a stored procedure/query that has the plain text version? How do you extract plain text in SQL? It's possible with a function, but it's a lot of work.

2. Processing the HTML blob can be slow. I would imagine for small HTML blobs it will be very fast, but there is certainly more overhead than just reading a plain text field.

3. HTML parsers don't always work well/they can be complex. The idea is that your users can be very creative and insert blobs that won't work well with your parser. I know from experience that it's not always trivial to extract plain text from HTML well.

I would propose what most email providers do:

1. Store a rich text/HTML version and a plain text version. Two fields in the database.
2. As is the use case with email providers, the users might want those two fields to have different content.
3. You can write a UI function that lets the user enter in HTML and then transforms it via the application into a plain text version. This gives the user a nice starting point and they can massage/edit the plain text version before saving to the database.

回答4:

I would suggest to store it in the HTML format, since is the richest one in this case, and remove the tags when obtaining the data for other formats (such PDF, Latex or whatever). In the following question you'll find a way to remove tags easily.

Regular expression to remove HTML tags

From my point of view, storing data (original and downgraded) in two separate fields is a waste of space, but also an integrity problem, since one of the fields could be -in theory- modified without changing the second one.

Good luck!

回答5:

1. Always store the source, in your case it is markdown.
2. Also store the formats that are frequently used.

3. Use on demand conversion/rendering for less frequent used formats.

   Explanation:

4. Always have the source. You may need it for various purpose, e.g. the same input can be edited, audit trail, debugging etc etc.

5. No overhead for processor/ram if the same format is frequently requested, you are trading it with the disk storage which is cheap comparing to the formars.

6. Occasional overhead, see the #2

回答6:

I think that what I'd do - if storage is not an issue - would be store the canonical version, but automatically generate from it, in persisted, computed fields, whatever other versions one might need. You want the fields to be persisted because it's pointless doing the conversion every time you need the data. And you want them to be computed because you don't want them to get out of synch with the canonical version.

In essence this is using the database as a cache for the other versions, but a cache that guarantees you data integrity.