The project will deliver a centralised company intelligence repository that enables easy storage and rapid retrieval of information relating to the activities, specialities and geographic scope of companies operating within the international real estate and investment sector.
To ensure compatibility with other systems, the database will need to be developed in SQL Server, with all data tools coded in .NET.
Over time, the database will provide a universal backbone for a number of public and private directories, intelligence systems and CRM tools and while these will not all form part of the initial phase of development, the long term context of the project may require some consideration.
The business already holds significant data in various data silos and non-integrated systems. Once the new structure and tool set is complete, the existing data will be migrated, system-by-system. The new database will initially hold 5 types of information:
- Company: company-wide information stored at organisation level
- Office: branch-level information for one or more offices in a Company
- Contact: information pertaining to an individual within an Office or Company
- Account: data that defines the parameters of any relationship with our business
- Activity: enquiries, meetings, event attendance and other transactional logs
During 2015, a piece of work was started to define the database table structure and the relationships between them. While this project was not fully completed, it does provide a useful starting point for the project.
Companies may have one or multiple office IDs, Account IDs or Contact IDs associated with them. Office IDs have a fixed association with a Company, but may have multiple Account or Contact IDs. Each Contact ID may potentially move between Offices, Companies and/or Accounts. Activity may be specifically associated with a Contact, Account, Office or Company.
1. Add Record
Critical to the organic growth of the system is the capability for research staff to quickly and easily add new contacts, offices and companies, with minimal effort or cross-checking of records.
- Mandatory Fields
Very few fields should be mandatory: Company Name and either email or telephone.
- Assumed Data
Where only one Contact or Office is added for a Company, data can be cross-populated automatically, in order to populate fields such Email, Phone 1 and 2 across both tables.
- Potential Matches
While the Company ID is the primary key for this data, there should be Ajax matching on certain fields, such as Company Name, URL, email and telephone number, to automatically check for existing records with the same value.
2. Bulk Import
A function should be created that allows bulk importing of data, provided it is in a compatible structure. This should be a CSV or Excel file, formatted to match a specified set of column headings.
3. Merge Records
As disparate data sources are cleaned up over time, it may be necessary to merge specific records. Ideally, where there are different values for the same field, it should be somehow possible to select which value to retain and which to overwrite
4. Edit Record
A single page input form should be used to for editing any data that can be modified manually by the user (with a log of any changes). There is already an existing page for managing our customer information - it may make sense to restructure this page to accommodation any new data fields.
5. Auto Create
New data is available daily, as a result of our client campaigns, our outreach activity and our internal marketing. Where responses are created and no match can automatically be found, a new record should be created in the Company / Office / Contact table.
6. Search Database
The search module is a key component of the initial functionality. A simple form should allow fast retrieval of matching records, using a variety of parameters to filter the results set, with matches displayed in an easy to digest format.
Search fields should at a minimum include the following: Company Class, Country, Geographic Focus, Specialisation, Geographic Areas, Priority, Call List, Status (see below).
The results should be presented in a snapshot view, with key data compiled into an easy to digest format, with additional information available as an Ajax call on hover, allowing the user to expand on the information initially presented. Tick a tickbox to add new matches to the pre-selected Call List.
7. Call List
Call lists are lists of companies to contact in relation to a specific piece of work. These should be managed from a Job Management page, where they can be created, deleted or retrieved.
The retrieve function should be exactly the same as the Search Database results, wwith an additional status filter and a status for each record in relation to the job in question. The statuses should be a simple traffic light system, with Success, Progress, Fail as the 3 settings.