Principles relevant to working with subject matters should be used for relational database data storage structure development, that is, for development of tables, table fields, field properties and relationships between the tables. There are exceptions to this principle that involve large data archives. These exceptions are beyond the scope of this article.
Examples of subject matters are organizations, people, ingredients, and products. As part of the development process, subject matters become tables and each subject matter's characteristics relevant table fields.
When data storage structure is built by connecting the subject matters that the application deals the resulting structure is both easier to understand and work with. Further, many-to-many relationships can usually be set up so that one of the subject matters tracked will perform the intermediate table role.
Each subject matter (people, organizations, etc) must be in a separate table so that the fields characterize that and only that subject matter. There should be only one main table per subject matter. However, characteristics, or table fields, may draw information from other tables, called lookup tables, in cases where there are several alternatives available. For example, organizations and people are separate subject matters that are relevant to a project's database. If one of the primary subject matters that the database tracks is organizations (or companies), then people who work for an organization are one of organization's characteristics (in addition to organization's name, address, etc). Because there may be more than one person per organization, people table should be linked to organization's table as lookup table (in one-to-many relationship). Similarly, people's table may contain both Yes or No type of fields and linked tables that characterize people.
Subject matters may function in different capacity. For example, people may be employees and have supervisors (both part of the people table - lets name it tblPeople). In such case Supervisors field (which is part of tblPeople) draws information as lookup field from the same table that it is located in (which is tblPeople). Similarly, employees may have different roles - for example, employees may both perform tasks in some instances and assign tasks to others in other instances. Here, too, people-related lookup fields, which in this case are part of the task-related subject matter's table, draw information from the same table (tblPeople). Further, to enforce referential integrity (if that is desired) the relationships should be created using duplicate issues of the same table in the Relationships window (in our example tblPeople). In case of queries (that are used as data sources for forms or reports) the same principle applies, but specific details of relationship building depend on whether self joins or multiple joins from one table to another are used.
There are exceptions to the "one subject matter in one table only" rule. Additional table is needed in the following cases: when the amount of fields is so large that it does not fit in only one table, or if certain entries have to be isolated for security reasons, or if the table contains entities with large number of different characteristics. For example, is supplier and customer organizations have both large number of very different characteristics, then it may make sense to have them in different tables. However, in most instances that is not the case.
When data storage structure (tables, table fields and relationships) is developed by following information flow, problems can result.
For example, if a database tracks different organizations (suppliers and customers) and data storage structure (tables, table fields and relationships) is developed by following information flow it may seem that different tables are needed for suppliers and customers. It seems only logical to think this way because the database does track movement of goods and services from suppliers to producers (who use the database) and then to the customers who use the final products. So, it seems like information regarding the suppliers and customers should be contained in different tables. Similarly, if within an organization the database deals with, let say, employees and supervisors, then it may seem like a good idea to have different tables for employees and supervisors. Or, if the database tracks how people from different organizations interact (lets say, as buyers and sellers, or as nominators and nominees) then it may seem like a good idea to have different tables for such entities.
However, closer examination usually shows that most fields match for the same subject matter, whether the subject matter is organizations or people or something else.
Separating a single subject matter (for example, organizations) into different tables increases complexity and decreases flexibility and number of options available for different operations. What may happen as a result is that the resulting database becomes difficult to develop so that information can be entered, queried, modified and displayed as needed. Of course, consequences are even more troublesome if normalization rules are violated so that any table contains fields that characterize more than one subject matter (for example, both people and organizations). That can also happen when data storage structure is developed by following information flow.
Thus, building data storage structure so that there is one and only one main table per subject matter helps to make application development a more efficient and productive process. In addition, in most cases there is no real need to set up more than one main table per individual subject matter. After all, whether an organization is a supplier or a customer, or whether a person works for a supplier or for a customer is simply a characteristic. Main subject matter tables can have literally tens of Yes or No type fields that characterize the subject matter included in the table. In addition, main subject matter tables can have literally tens of lookup tables attached to them for handling characteristics with more than one alternative. These lookup tables should contain applicable main subject matter characteristics' alternatives (for example, red, blue, yellow, etc). Each such lookup table should have its own automatically incremented primary key field in addition to the rest of the fields. Further, lookup table subject matter can also be characterized by using Yes or No type fields and lookup tables. Such structure can easily be worked with so that all the application development options are readily available while the necessary time and effort input is minimized.
