| lettings_ledger | CREATE TABLE `lettings_ledger` ( `ledgerID` int(11) unsigned NOT NULL AUTO_INCREMENT, `transactionID` int(11) unsigned NOT NULL, `activityID` int(11) unsigned NOT NULL, `transactionType` varchar(10) NOT NULL, `propertyID` int(11) unsigned NOT NULL, `tenancyID` int(11) unsigned NOT NULL, `ledgerType` varchar(20) NOT NULL, `ledgerTypeID` int(11) unsigned NOT NULL, `linkTable` varchar(43) NOT NULL, `linkField` varchar(20) NOT NULL, `linkID` int(11) unsigned NOT NULL, `itemDescription` varchar(100) NOT NULL, `debitAmount` decimal(10,2) unsigned NOT NULL, `creditAmount` decimal(10,2) unsigned NOT NULL, `documentType` varchar(10) DEFAULT NULL, `documentNumber` int(11) unsigned DEFAULT NULL, `statementNumber` int(11) unsigned NOT NULL DEFAULT '0', `displayItem` tinyint(1) unsigned NOT NULL DEFAULT '1', `dateCreated` datetime NOT NULL, `recordCreatedBy` int(11) unsigned NOT NULL, `originalLedgerID` int(11) unsigned NOT NULL, PRIMARY KEY (`ledgerID`), KEY `tenancyID` (`tenancyID`), KEY `propertyID` (`propertyID`), KEY `statementNumber` (`statementNumber`) USING BTREE, KEY `linkID` (`linkID`) USING BTREE, KEY `ledgerType` (`ledgerTypeID`,`ledgerType`) USING BTREE, KEY `idx_lt_sn` (`ledgerType`,`statementNumber`), KEY `idx_tranid` (`transactionID`), KEY `idx_dn` (`documentNumber`) ) ENGINE=InnoDB AUTO_INCREMENT=87691 DEFAULT CHARSET=latin1 MariaDB [jb_thebristolresidentiallettingcoltd]> show index in lettings_ledger; +-----------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | lettings_ledger | 0 | PRIMARY | 1 | ledgerID | A | 63152 | NULL | NULL | | BTREE | | | | lettings_ledger | 1 | tenancyID | 1 | tenancyID | A | 3007 | NULL | NULL | | BTREE | | | | lettings_ledger | 1 | propertyID | 1 | propertyID | A | 2428 | NULL | NULL | | BTREE | | | | lettings_ledger | 1 | statementNumber | 1 | statementNumber | A | 7016 | NULL | NULL | | BTREE | | | | lettings_ledger | 1 | linkID | 1 | linkID | A | 12630 | NULL | NULL | | BTREE | | | | lettings_ledger | 1 | ledgerType | 1 | ledgerTypeID | A | 5741 | NULL | NULL | | BTREE | | | | lettings_ledger | 1 | ledgerType | 2 | ledgerType | A | 12630 | NULL | NULL | | BTREE | | | | lettings_ledger | 1 | idx_lt_sn | 1 | ledgerType | A | 36 | NULL | NULL | | BTREE | | | | lettings_ledger | 1 | idx_lt_sn | 2 | statementNumber | A | 15788 | NULL | NULL | | BTREE | | | | lettings_ledger | 1 | idx_tranid | 1 | transactionID | A | 63152 | NULL | NULL | | BTREE | | | | lettings_ledger | 1 | idx_dn | 1 | documentNumber | A | 9021 | NULL | NULL | YES | BTREE | | | +-----------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | CREATE TABLE `user` ( `userID` int(11) NOT NULL AUTO_INCREMENT, `userActive` varchar(10) NOT NULL DEFAULT '', `displayName` varchar(50) NOT NULL DEFAULT '', `username` varchar(50) NOT NULL DEFAULT '', `password` varchar(200) DEFAULT NULL, `hash` varchar(91) DEFAULT NULL, `jupixRepresentative` tinyint(1) unsigned NOT NULL DEFAULT '0', `neverDeactivate` tinyint(1) unsigned NOT NULL DEFAULT '0', `defaultDepartment` char(3) NOT NULL DEFAULT '', `defaultOfficeID` int(11) NOT NULL DEFAULT '0', `sessionDepartment` char(3) NOT NULL DEFAULT '', `sessionOfficeID` int(11) NOT NULL DEFAULT '0', `userEmailAddress` varchar(200) NOT NULL DEFAULT '', `userTelephoneNumber` varchar(30) NOT NULL DEFAULT '', `userMobileNumber` varchar(30) NOT NULL DEFAULT '', `userValidEmailAddress` tinyint(1) NOT NULL DEFAULT '0', `userValidMobileNumber` tinyint(1) NOT NULL DEFAULT '0', `userEmailSESVerified` tinyint(1) unsigned NOT NULL DEFAULT '0', `userEmailSESSentVerify` tinyint(1) unsigned NOT NULL DEFAULT '0', `userInitials` varchar(10) NOT NULL DEFAULT '', `userDOB` date NOT NULL DEFAULT '0000-00-00', `userQualifications` varchar(30) NOT NULL DEFAULT '', `userJobTitle` varchar(100) NOT NULL DEFAULT '', `userAdvancedSignatureBlockHTML` text, `passwordExpiryDays` tinyint(4) NOT NULL DEFAULT '0', `passwordExpiryDate` date NOT NULL DEFAULT '0000-00-00', `passwordResetHash` varchar(91) DEFAULT NULL, `passwordResetTimestamp` varchar(12) DEFAULT NULL, `passwordResetEmailAddress` varchar(256) DEFAULT NULL, `canResetPassword` tinyint(1) unsigned DEFAULT '1', `securityQuestionID` tinyint(2) unsigned DEFAULT NULL, `securityQuestionAnswer` varbinary(512) DEFAULT NULL, `lastActiveDateTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `userSingleLogin` tinyint(1) NOT NULL, `userIPRestricted` tinyint(1) NOT NULL, `userSessionID` varchar(100) NOT NULL DEFAULT '', `userIPAddress` varchar(50) NOT NULL, `cookieHash` varchar(255) DEFAULT NULL, `mobileCookieHash` varchar(255) DEFAULT NULL, `fusionHash` varchar(255) DEFAULT NULL, `mobileDiaryActive` tinyint(1) unsigned NOT NULL DEFAULT '0', `kayakoUserID` smallint(5) unsigned NOT NULL, PRIMARY KEY (`userID`), KEY `idx_ua_dn` (`userActive`,`displayName`) ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=latin1 MariaDB [jb_thebristolresidentiallettingcoltd]> show index in user; +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | userID | A | 26 | NULL | NULL | | BTREE | | | | user | 1 | idx_ua_dn | 1 | userActive | A | 6 | NULL | NULL | | BTREE | | | | user | 1 | idx_ua_dn | 2 | displayName | A | 26 | NULL | NULL | | BTREE | | | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | property | CREATE TABLE `property` ( `propertyID` int(11) unsigned NOT NULL AUTO_INCREMENT, `propertyURN` bigint(20) unsigned NOT NULL, `propertyActive` varchar(10) NOT NULL DEFAULT '', `clientID` int(11) unsigned NOT NULL, `ownerID` int(11) NOT NULL DEFAULT '0', `recordLocked` tinyint(1) NOT NULL DEFAULT '0', `propertyFileID` int(11) NOT NULL, `mediaStatus` smallint(4) unsigned NOT NULL DEFAULT '1', `referenceNumber` varchar(20) NOT NULL, `addressName` varchar(50) NOT NULL, `addressNumber` varchar(30) NOT NULL, `addressStreet` varchar(50) NOT NULL, `address2` varchar(50) NOT NULL, `address3` varchar(50) NOT NULL, `address4` varchar(50) NOT NULL, `addressPostcode` varchar(15) NOT NULL, `country` varchar(40) NOT NULL, `displayAddress` varchar(100) NOT NULL DEFAULT '', `internationalRegionID` int(10) unsigned DEFAULT NULL, `officeID` int(11) NOT NULL DEFAULT '0', `propertyDepartment` varchar(12) NOT NULL, `ourProperty` tinyint(4) NOT NULL, `occupation` tinyint(2) NOT NULL, `viewingCarriedOutBy` varchar(10) NOT NULL, `viewingOwnerConfirmation` tinyint(1) NOT NULL, `viewingNotes` text, `directions` text, `mainSummary` varchar(1000) DEFAULT NULL, `smsSummary` varchar(500) DEFAULT NULL, `advertSummary` varchar(1000) DEFAULT NULL, `advertSummary2` varchar(1000) DEFAULT NULL, `propertyFeature1` varchar(100) DEFAULT NULL, `propertyFeature2` varchar(100) DEFAULT NULL, `propertyFeature3` varchar(100) DEFAULT NULL, `propertyFeature4` varchar(100) DEFAULT NULL, `propertyFeature5` varchar(100) DEFAULT NULL, `propertyFeature6` varchar(100) DEFAULT NULL, `propertyFeature7` varchar(100) DEFAULT NULL, `propertyFeature8` varchar(100) DEFAULT NULL, `propertyFeature9` varchar(100) DEFAULT NULL, `propertyFeature10` varchar(100) DEFAULT NULL, `vendorLoginActive` tinyint(1) NOT NULL, `keySetName` varchar(100) DEFAULT NULL, `keyCustodian` smallint(4) unsigned DEFAULT NULL, `keyDateOut` date DEFAULT NULL, `keyDueIn` date DEFAULT NULL, `keyNotes` text, `boardStatus` smallint(4) unsigned DEFAULT NULL, `boardContractor` int(11) unsigned DEFAULT NULL, `boardNotes` text, `epcProvider` int(10) unsigned NOT NULL, `epcNotRequired` tinyint(3) unsigned NOT NULL DEFAULT '0', `vendorContactNotRequired` tinyint(1) unsigned NOT NULL DEFAULT '0', `marketingContractSigned` tinyint(1) unsigned NOT NULL, `draftParticularsSent` tinyint(1) unsigned NOT NULL, `draftParticularsSigned` tinyint(1) unsigned NOT NULL, `dateCreated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `recordCreatedBy` int(11) NOT NULL DEFAULT '0', `dateUpdated` datetime NOT NULL, `recordUpdatedBy` int(11) unsigned NOT NULL DEFAULT '0', `dateInstructed` date NOT NULL, `dateExclusiveUntil` datetime DEFAULT NULL, `originalPropertyID` varchar(20) DEFAULT NULL, `originalBranchID` varchar(20) DEFAULT NULL, `portalState` tinyint(3) unsigned DEFAULT NULL, `childPropertyID` int(11) unsigned DEFAULT NULL, `availabilityID` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`propertyID`), KEY `officeID` (`officeID`), KEY `referenceNumber` (`referenceNumber`), KEY `addressName` (`addressName`), KEY `addressStreet` (`addressStreet`), KEY `addressNumber_2` (`addressNumber`,`addressStreet`), KEY `propertyURN` (`propertyURN`), KEY `propDept` (`propertyDepartment`), KEY `propertyActive` (`propertyActive`), KEY `ownerID` (`ownerID`) ) ENGINE=InnoDB AUTO_INCREMENT=3206 DEFAULT CHARSET=latin1 MariaDB [jb_thebristolresidentiallettingcoltd]> show index in property; +----------+------------+-----------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+-----------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | property | 0 | PRIMARY | 1 | propertyID | A | 2956 | NULL | NULL | | BTREE | | | | property | 1 | officeID | 1 | officeID | A | 4 | NULL | NULL | | BTREE | | | | property | 1 | referenceNumber | 1 | referenceNumber | A | 2956 | NULL | NULL | | BTREE | | | | property | 1 | addressName | 1 | addressName | A | 164 | NULL | NULL | | BTREE | | | | property | 1 | addressStreet | 1 | addressStreet | A | 2956 | NULL | NULL | | BTREE | | | | property | 1 | addressNumber_2 | 1 | addressNumber | A | 985 | NULL | NULL | | BTREE | | | | property | 1 | addressNumber_2 | 2 | addressStreet | A | 2956 | NULL | NULL | | BTREE | | | | property | 1 | propertyURN | 1 | propertyURN | A | 2956 | NULL | NULL | | BTREE | | | | property | 1 | propDept | 1 | propertyDepartment | A | 2 | NULL | NULL | | BTREE | | | | property | 1 | propertyActive | 1 | propertyActive | A | 6 | NULL | NULL | | BTREE | | | | property | 1 | ownerID | 1 | ownerID | A | 40 | NULL | NULL | | BTREE | | | +----------+------------+-----------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | lettings_statement | CREATE TABLE `lettings_statement` ( `statementID` int(11) unsigned NOT NULL AUTO_INCREMENT, `portfolioID` int(11) unsigned NOT NULL, `propertyID` int(11) unsigned NOT NULL, `tenancyID` int(11) unsigned NOT NULL, `contactID` int(11) unsigned NOT NULL, `statementNumber` int(11) unsigned NOT NULL, `propertyStatementNumber` int(11) unsigned NOT NULL, `portfolioStatementNumber` int(11) unsigned NOT NULL, `invoiceNumber` int(11) unsigned NOT NULL, `openingBalance` decimal(10,2) NOT NULL, `moneyWithheld` decimal(10,2) unsigned NOT NULL, `taxReliefCarriedForward` decimal(10,2) NOT NULL, `remark` text, `emailStatus` varchar(7) NOT NULL DEFAULT '', `reversalID` int(11) unsigned NOT NULL DEFAULT '0', `dateCreated` datetime NOT NULL, `recordCreatedBy` int(11) unsigned NOT NULL, PRIMARY KEY (`statementID`), KEY `propertyID` (`propertyID`) USING BTREE, KEY `portfolioID` (`portfolioID`), KEY `statementNumber` (`statementNumber`), KEY `idx_pid_sn` (`portfolioID`,`statementNumber`), KEY `reversal` (`reversalID`) ) ENGINE=InnoDB AUTO_INCREMENT=4566 DEFAULT CHARSET=latin1 MariaDB [jb_thebristolresidentiallettingcoltd]> show index in lettings_statement; +--------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | lettings_statement | 0 | PRIMARY | 1 | statementID | A | 4279 | NULL | NULL | | BTREE | | | | lettings_statement | 1 | propertyID | 1 | propertyID | A | 2139 | NULL | NULL | | BTREE | | | | lettings_statement | 1 | portfolioID | 1 | portfolioID | A | 2139 | NULL | NULL | | BTREE | | | | lettings_statement | 1 | statementNumber | 1 | statementNumber | A | 4279 | NULL | NULL | | BTREE | | | | lettings_statement | 1 | idx_pid_sn | 1 | portfolioID | A | 2139 | NULL | NULL | | BTREE | | | | lettings_statement | 1 | idx_pid_sn | 2 | statementNumber | A | 4279 | NULL | NULL | | BTREE | | | | lettings_statement | 1 | reversal | 1 | reversalID | A | 24 | NULL | NULL | | BTREE | | | +--------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+