Tersus: Records, Rows or Table?

Records, Rows or Table?Date: Jun 25, 2008

There are at least 3 ways to create a display table (in the client side) based on records read from a database table or view (in the server side):

Pass Records – Read the records on the server and pass them to the client side. On the client side, convert each record to a display row, and add the rows to the display table.
Pass Rows – Read the records and convert each record to a display row on the server side. Pass the created rows to the client side, and there add them to the display table.
Pass the Table – Read the records, convert each record to a display row and use the rows to create the display table on the server. Pass to the client the created table.

Although the 1st method (passing the records) seems to minimize the amount of data transferred from the server to the client, we recommend the 2nd method (passing the rows), for several reasons:

The overhead of a display row is not very big. On the other hand, many times the rows will contain only some of the fields of the database records, and this selective transfer of fields may even make each row smaller than the corresponding database record.
The selective transfer of data from the server to the client has a security advantage as well. It allows us to avoid the transfer to the client side of data that the user is not supposed to see. The alternative – transferring all the information to the client side and filtering there, based on user permissions, what information is displayed to the user – is both less efficient and less secure.
Many times there is a need to enrich each row with data coming from related database tables/views (e.g. if a field in the record is an id of a related record, and we want to display the content of fields from this related record). If we transfer the database records to the client side and there start a process of enriching each row with its related data, we will need additional services to read the related records. On the other hand, if we create the enriched row on the server, everything can be made within a single service. Specifically, if performance is an issue, use a database view that already includes all the additional fields related to the records (thus performing all data retrieval by a single database query).
The 3rd method (passing the whole table) is also efficient and secure, but is less modular. If we only transfer the rows, we can re-use the process when the display table should be retained (e.g. to preserve its filtering and sorting settings) while replacing only its content (the rows). We can also add rows to an existing display table.