When I create tables that are used in the interface to hold large strings of data (like a large blob or XML from external system or AX report), I put those fields in a separate table linking to the original 'metadata' table through a unique key. This would make the typical one table interface turn into two tables: one to hold the metadata for the user and the other to hold the large files.
In using this design, there are many upsides but two primarily stick out that I would like to talk about:
The first is the obvious performance gain in the form used to manage the records in the interface. When, for example, 20 records are retrieved from the database, records that do not contain an extremely large fields will come back faster.
The second is in regards to the maintenance. A user may not need the data stored in the large fields after a few weeks but would still want to maintain the history of that data coming into the system. To do this on a single table structure would require a cleanup job to specifically clear out the field holding the large data. Doing this in a two table structure would allow the entire record to be deleted, but leave the original record in place. A simple inner join between the two tables would show all records in those tables that still have the data in place.
Discuss your preferred way to do this below in the comments section...
No comments:
Post a Comment