Sometimes database tables grow and require some columns to be moved to a separate table. Find out when this may be a good idea!
TL;DR: As a database table grows, certain columns can become candidates for being moved into a separate table and referred to by a particular ID in the original table. To know when this is a good idea, you need to determine the purpose of the data that is in the column in question and the potential for it to expand and evolve over time.
Determine the Purpose of the Data
Suppose you had a small application using a single table, like the one shown below:
An example database table.
This table is simply used for storing user information. You have a user ID, last name, first name, and user type. All of these seem good, as long as the number of user types is small and doesn’t change very often. However, what if different user types need to be added on a somewhat regular basis over time?
Since the user type is currently a string of text (character varying in database terms), the initial adding of a user or the updating of the user type requires that particular string of text to be entered. There is a chance (especially if an update is done via a manual query) that the string could be mistyped slightly. This creates a new user type, which would exclude the user from being in the originally intended group!
Using an ID Instead
Now that the user type column is becoming problematic, you need to find a way to help keep the data in the column consistent with the actual user types. You can simply change this to a number; however, this will require that anyone adding or updating records in the table know what those number mean (e.g., 1 is for administrator, 2 is for premium user, and so on).
Another method would be to change it to a number and add a user type description to the table as well, but this is now adding another column to keep track of in this table, and could fall prey to the type description being entered incorrectly.
Moving a Column to a Table
Since you want to have the user type be a number, but still want to know what that number means, user type information is a good candidate to be moved to its own table with an ID as the primary key and a description column. The ID can then be referenced in the original users table. This helps ensure that both the user and user type data are kept consistent and accurate.
The resulting tables are shown below:
An example of two tables working together.
Notice that you can now simply point to the user type ID in the user_types table. This table can now be referenced if you need to know what that number means, and keeps each of those types with a consistent ID and description.
Get Your Own Database
Whether your database tables will be simple or complex, Morpheus Virtual Appliance is a tool that allows you manage heterogeneous databases in a single dashboard. With Morpheus, you have support for SQL, NoSQL, and in-memory databases across public, private, and hybrid clouds. So, visit the Morpheus site for pricing information or to create a free account today!
