Creating The MySQL Data Tables.
Every datamanager type module must manage at least 1 MySQL table. The whole purpose of the data manager is to allow the user to manage records such as invoices, clients, sales leads, etc... Well, these records are stored in MySQL tables. This section describes how to create your MySQL data table or tables. It is imperative that you design your MySQL tables correctly and that you create indexes for the correct fields because all the code and parameters that are available in the datamanager depend on how your MySQL tables are designed. If you design your MySQL tables poorly and then decide to change them after you have already coded your PHP files, then you are possibly looking at major PHP re-coding in order to get everything to work as you need it to. Also, MySQL database design is outside the scope of this publication so we will not cover it here. If you are a MySQL beginner, we recommend that you read up on good database design. With that said, what we can do is list the VConsole requirements for your MySQL tables and we can give you some tips on good database design.

• VConsole Requirements. When creating the MySQL tables that will store your precious data, you must adhere to the following rules. Although VConsole is designed to be as flexible as possible when it comes to the design of your MySQL tables, it will not work properly if you violate these rules. These rules only apply to all MySQL tables that are managed by datamanager type modules.
  • All MySQL tables MUST contain a primary key. This key must be an INT or BIGINT field (preferrably INT unless you plan on having more than 4 billion records) that has AUTO INCREMENT enabled. We recommend that you name this primary key field id. This primary key must be defined in the <mysql_tableindex> parameter in the dms.php file (see below)
  • All MySQL tables may or may not contain a vccolor field. However, the vccolor field MUST NOT be used by you as a regular data field. This field name is reserved and is used for the record coloring feature of VConsole.
  • No MySQL table may contain any field that begins with extrafield followed by 1 or more digits. VConsole creates fields on the table called extrafield1, extrafield2, etc.. when users create extra fields in the module.
  • All MySQL tables must have indexes created for fields that are used in table joins. This should be a standard practice anyway. (DO NOT create indexes for fields that are not used in table joins unless it is a full text search index and you plan on using the full text searching capabilitles of MySQL)
  • Do not modify any of the existing tables in the database unless you know what you are doing or you are instructed to do so by a VSource technician or developer.
  • If you want to use the record coloring features of VConsole, you must create a vccolor field VARCHAR(15) NOT NULL
     
• Tips for Creating Your MySQL tables. When creating your MySQL tables, we recommend applying the following tips to your design.
  • Avoid duplicating data if possible. If you are creating 2 modules that are integrated or connected, avoid duplicating data. Instead, use table joins and common key fields in your SQL queries to get the data that you need.
  • When writing your SQL queries for the data manager, make sure that they are optimized. Some SQL statements are faster than others and they return the same results. SQL statements will directly affect performance of your application if not written correctly or not optimized. Simple queries don't suffer from this problem as much as complex queries or queries with table joins.
  • If using VARCHAR fields, only make them as long as they have to be. Don't just use VARCHAR(255) when the longest string that you will ever store is 50 characters. This is a common bad practice among MySQL designers.
  • Avoid using BIGINT for your primary index / AUTO INCREMENT fields. INT is a much more efficient use of MySQL resources and will hold up to 4 billion records.
  • Don't create indexes if you never use them as this will only take up additional memory and disk space unnecessarily.
  • If, in your data table, you store Large BLOBS (like images) that you do not intent to display in any column in the data manager you should not use SELECT * FROM `table` WHERE 1 in your data query as this may use up a temendous amount of memory unnecessarly. Instead, query for only the columns that you intend to display. For example: SELECT `field1`, `field2` FROM `table` WHERE 1
  • I know that this goes without saying, but we will mention it here anyway. Backup your MySQL database regularly. Remember, MySQL is where all the data is stored. It is essential that you maintain and test proper backups. We recommend a backup program like Snap DP Data Protection to backup your MySQL tables.