Designing tables defines the efficiency of your software. Most software developers agree that the database design is the first step to engineering software. The way you define your tables determines how you design your software. After you’ve designed your tables, you then create what is called a “data dictionary.” A data dictionary is a document that outlines your table designs, the data type for each column and a brief explanation of each field.
Lay Out Your Tables as Categories
Typically, you start out your data dictionary with a list of tables. How you categorize and lay out your dictionary is up to you. If it’s a large database, you might want to create categories for organizations or functionality and then add tables as sub-categories. For smaller databases, you can list your tables alphabetically as categories. Listing each table alphabetically makes it easy for other people to reference your table design and find what they are looking for in the documentation.
Below is a small example of several tables you might have if you owned an ecommerce store:
Accounts: Customer account with email and password as account credentials
Customer: Contains all customer information
CustomerAddress: Has customer addresses and categorizes them by address type (home, office, billing, shipping)
Orders: Customer orders
Products: Our products
This is an extremely small database, but it works for this example. The tables are listed in alphabetical order with a small explanation of what the table contains. You can just give a basic overview at this level, because the data dictionary will describe the columns in more detail.
You can get a list of your tables either in the database graphical interface tools or in the command line. For instance, Microsoft SQL Server Management Studio lets you connect to the database and list your tables. MySQL has the MySQL Workbench, which also lets you manage tables, databases and security.
Define Your Table Data Definitions
A table definition describes each column. You first define the data type and then write a small description for the column. The following table gives you a data definition for the “Customer” table:
CustomerId: int, primary key, not null: Auto-incrementing customer ID
Name: varchar(100), null: Customer full name.
Zip Code varchar(10), null: Country-specific zip code
SignUpDate datetime, getDate() default, not null: Date when the customer signed up
In this example, there are four columns: the customer ID, customer name, zip code and the signup date. The first column (CustomerId) is marked as the primary key, integer data type and does not allow any nulls. Primary key columns cannot contain null values anyway. The last section gives you a description for the column. The description is “an auto-incrementing customer Id.” If you know database design, you know that columns can be set to auto-increment by 1 (or another value), so you know that you always have at least one unique column in the table. The “int” data type is numeric, so the database will continue adding 1 to the column automatically each time a record is inserted.
The second column is “Name.” For this example, the customer’s full name is stored with a maximum 100 characters indicated by the varchar(100) data type. Typically, database designers separate first and last name values into separate columns.
Zip code is also stored as a character varchar(10) column. In the US, zip codes are numeric, but if you plan to sell to people outside the US, you need to define your zip code columns to allow characters.
Finally, the SignUpDate is set as a datetime data type. The “getdate()” retrieves the current system date and time in MSSQL. In this example, the database automatically sets this column each type a row is inserted into the database. This is indicated by the “default” definition.
Notice all of these columns have a brief explanation next to the column name and data type. This brief description allows other designers to read your data dictionary to get a better understanding of how the database is designed. It’s also helpful for other software developers who need to make changes to the software or the database design.
You need one of these table definitions for each table located in your database.
Every database needs some kind of security. You can choose very simple security with a user name and password that has full access to the specified database. You can also get very granular with security. Granular security defines user names mapped to specific database functionality. For instance, one user has read access only to perform “select” statements and another user can read and update records with “select” and “update” statements.
Your data definition file doesn’t need to contain the passwords, but you should define your security model for other developers and database administrators. This can greatly reduce the time it takes to troubleshoot certain security issues. It also helps developers understand which user to use when performing certain actions on the database.
Organizing Your Data Definition Documentation
When your data definition documentation has security included, you need to find a secure place to store your files. This can be a shared resource on your network with security on the share or just storing the definition file locally without sharing it without permissions.
Your data definition should be included with your software documentation. Software documentation helps other developers manage your software without too much personal training. Documentation along with code comments reduce the amount of time needed for training.
Your data definition can grow to several hundreds (even thousands) of rows. Keep it organized or the definitions grow out of control and disorganized, making it more difficult to read the database definition file instead of just browsing the database structure. This definition documentation will keep your software and tables efficient and reduce redundancy on your tables.