Data modeling for boosting record insertion operations

Data modeling for boosting record insertion operations

  
Consider the following two informal entity descriptions:

ClientServiceA
- Id
- ClientId
- ServiceId
- Price
- Period
- TrialDays

ClientServiceB
- Id
- ClientId
- Services // service info. serialization field: <serviceId>;<price>;<period>;<trialdays>|<serviceId>;...

Imagine that, for each client, 500 services would need to be related, causing the insertion of 500 ClientServiceA records and 1 ClientServiceB record. In this case, the gain in insertion operations can be as much as 350 times (!!) faster for ClientServiceB entity.

Remarks:
- The implementation of an access API is advisable (for string manipulation while serializing for insertion, for parsing, for updating values, ...). These parsing operations also do not pose performance issues.
- Table join operations with serviceId foreign key are unusable. The refered API could provide a method for creating a list of service id's ready for using in an advanced query (as an 'inline expanded' text parameter) thus having a similar result to the 'join' operation:

select * from Service where Service.id IN @list