This diagram shows necessary and sufficient modules required for managing a business and details of data content.
The extended universal schema of information control system in any business
During the break down of functional blocks into smaller parts, the result becomes a product that is composed of additional information blocks. Let’s call a diagram – Data Management. There are arrows connecting the functional blocks and information blocks.
Analysis of the Business Module (BM) starts by following the arrow from BM to Employees and Contractors. These information entities represent people dealing directly with the business operations. There is an arrow pointing to the Safety Management (SM). In modern businesses it is one of the most essential parts of management. Without well established safety management and quality control, businesses are likely not to succeed.
Another arrow is pointing to the Production Management (PM) block, a database holding all internal business related aspects; business rules, operations and principles.
The last arrow points to a Sales Management block, a database gathering information controlling financial aspects of business in connection with clients and customers.
Two information entities are located under this block: Employees and Contractors. As it was told above these can be seen as the figurative muscles of business, intellectual and physical properties. These information blocks hold data about people and their relations to business. What is the difference between them though? If people belong to an Employees section, they are being hired on permanent terms and get paid on a regular basis. They are also receiving benefit packages. Thus, the Company takes care of taxes for its employees. As for Contractors, their role is only to proceed with resolving business tasks, receiving rewards and taking care of different taxes themselves.
Addresses & Contacts
If you look close at Employees and Contractors, you may notice something that has a commonality between people from these different blocks: Addresses and Contacts. In each block the person has an address and a contact data. So far we reached the lowest information block, supplying requests for arrows (queries) coming from the top level of management.
What is the composition of this information block? Despite its simplicity, incompetent developers from monopolistic software development companies at the beginning of their success had founded a bad tradition to overcomplicate things by creating cumbersome, unusable, and user-unfriendly software applications with limited functionality for managing addresses and contacts of people and businesses.
Take any normal person who lives somewhere in the world. Any normal place in the world has its own unique geographical attributes. In a general case - it is a country which has an administrative division that includes a village, settlement, city, town, street, house, or a flat. In a modern industrial society, those territorial attributes are complimented with means of speed communication such as telephone, mobile phone, email, etc. For compiling these things into a logical and clear picture, we can now begin creating a correct information structure.
Let's first break them into two independent areas: Addresses and Contacts. What will be placed into the Addresses block? Shown below is the information that is neseccary and sufficient to define distinct allocation of the person.
Firstly - a Country. In addition to the country, there must be a predominant administrative territorial entity, called a region (a province or a state). Lastly, a name of the place of living which can be a village name, town name, city name, and eventually an actual address: a postal code, street name, house number, and a room number. It is absolutely neseccary for the Address area to have three reference tables: list of countries, list of areas, and list of places with a distinct attribute of the place type (village, town, city, etc.).
There are around 233 countries in the world. During the process of creating an address's system, it was found that each country is divided into major administrative divisions, the amount to which is estimated at about 4567 divisions +/- 100 due to permanent rearrangments or truncation of entities. As for places, the amount is already exceeding several millions. But is not a problem, they are still countable, and each place has its own area, each area has its own country. By defining only 3 entities it is easy to allocate any object in the world.
Itemizing of the distinct address requires street name, house and room number what is is a very private attribute. It is easy to store whole enumeration in one additional field of the address table. Each country has a different system for nomenclature of that detailed level, so for the purpose of universal storage it is more practical to call this information and the field the "address".
We hope that the description above will allow the diagram to be better understood.
The universal schema for database storing any addresses
Field PlaceType is here to define the type of the settlement, insofar as often happens that there exist different types of settlements with the same name in the area/state/province. Table Continents is added for the convenience of groupping and filtering data. In order to bring the design of the structure to a logical completeness, it is necessary to introduce detailed table in which unique details about the subject are usually stored - Addresses.
As you guessed, the subject (we are talking about the Id_Object field in the Object_Addresses table) can be either the Employee or the Contractor. Moreover, this principle of data storage is also suitable for managing miscellaneous data (Clients), suppliers (Vendors), competitors and so on. In real life people usually deal not with abstract client, but with a representative of a client, that is, a specific person. Address data for such cases can be simply stored in the presented database.
Of course, you can infinitely complicate and modify this scheme, but the essence remains the same - any person can have several residential addresses, he definitely has a home address, may have a country house address, a mistress address, and so on. Thus, the presented method of storing information will provide you a universal way to collect most useful information, the value of which you can evaluate yourself later.
A similar principle of splitting information into semantic blocks applies to the field of contact data of people. It will turn out to be a little simpler, but powerfull at the same time and usefull for data consolidation for various analyzes. You must have a reference table of types of operational communication - ContactType, in which you can store: "Phone", "Mobile", "Fax", "Email", "Skype", .. You need the Contacts table for direct storage of phone numbers and mail addresses, skype nicknames and so on. And the third Object_Contact table, similar to the one in the address scheme.
The universal schema for database storing any contacts
Now it is enough to create tables of managing of the subjects themselves: employees, contractors, clients, etc. and pass a pointer to a unique record number, respectively, in the Object_Address and Object_Contact table. As you can see, no difficulty is observed - only maximum flexibility, which creates the advantage of this system over some famous creations of incompetent artisans from programming (let's not point fingers - many who have read this far will understand). This approach does not limit the database developer of CRM management systems to three or four hard-coded columns of the database table for the addresses and contacts of the subjects! In the proposed database model, there are no restrictions on the number of addresses and contacts associated with one person.
You need to be registered for ability to comment articles.