YOUR OFSYS DB SHOULD BE OPTIMIZED FROM THE START
Creating new fields and inserting data in your Ofsys DB seems a simple and easy process. But you should consider the following:
Overtime, there will be an increase of the data stored in Ofsys (number of fields, values inserted, etc...)
Especially if you use a lot of complex Messages using Relational tables and complex queries.
So, the more you optimize your data in the beginning, the faster your application will work (Message preparation and sendings, searches, etc...)
CHOSING TEXTS or INTEGERS
- An integer = 4 bytes
- A text field = 2 bytes per character (because of UNICODE)
So Data formats are very important. Make the right choice since the beginning. Prefer numbers and codes rather than long texts (example : if you have a field "Source" to identify the source of registration of your Contact, you should prefer to use numbers ("1" is for Website registrations, "2" is for Facebook form registration, etc...) rather than putting the full text in Ofsys (it will increase your DB for nothing !).
Another example, if you have a field "auth", a unique token for each Contact, it's use to autheticate each visitor on the site.
Some "Auth" fields are build like that : 146da4c96ade57c231d795a589b3b48e (32 characters !!!). 32 characters x 2 bytes = 64 bytes !!. 64 bytes x number of Contacts in your DB (2 million) = 140 megabytes. A character string of 32 characters has a probability to be discovered of 1 out of 62 exponent 32, equivalent to (6232) !!! Equivalent to 2 followed by 57 zeros!! (trillions of trillions of trillions). Over complex for nothing !.
It is very possible to cut this chain to only 8 characters and you would still keep 1 chance to discover the code out of 218340105584896 (already enormous). And in terms of data storage, it would reduce from 140 Mb to 30 Mb just for this field alone. Furthermore, if you would prefer to use and interger for your code, it would reduce even further to 8 Mb of storage and keep a 1 chance out of 4 billion to discover the code.
The idea here is to demonstrate that the design of your DB should be optimized especially when you have large data. (maintain a strange key of 32 characters when you have 6 million contacts in your Ofsys DB will have bad consequences overtime. And this can be avoided without sacrificing security.
CHOSING "BOOL" or "INTEGER"
When you are looking for a "True-false" condition, use a boolean, not an integer where you will put either "0" or "1".
Example: let's say you have 3 fields "isPromoCodeUser" », « IsGoodName », « IsProductInBasket » that are real boolean but were created as integers, So the DB weight calculation is : 12 bits (x (Contact in your DB) 2 million = 26,7 Mb) while it could have been 3 bits = 2.5 mb in total.
ARE YOU SURE ALL YOUR FIELDS ARE ACTIVE ?
Each empty field represents an overhead of 4 bytes in an SQL table. So if you have 10 empty fields it can represent again hundreds of Mb!!!.
NEVER PUT KEEP large blocks into the ContactList table fields without setting up a clear cleaning procedure!
Any usage of full HTML blocks into fleids requires a parallel implementation of a data cleaning procedure. We are authorised to verify and to suspend the account if large HTML blocks are not cleaned in a systematic manner and create severe extra server loads.
By experience, it is possible to reduce the size of your non optimized DB by 75% ! in most cases. Ofsys will work much faster, and the system will be very pleasant to use for all.
Optimizing a small DB is a serious job, and designing and optimizing a large DB is an ART !