Database design, as the name might suggest, is much like house design, though the term also can be used to refer to actual database construction. The design process is something of a blueprint that outlines a database's details, from relationships between tables to what information is important and how the data will be implemented. Aside from helping the builder know what tables and information to collect, a design uses naming conventions, and spelling errors are checked before the database is completed. The database also goes through normalization, which seeks to remove redundancy, during the design process. Without first working out a design, a database creator can easily mess up the order of tables or the primary key for tables, or simply miss a few sections, among a slew of other potential errors.
The first step of database design is to know the purpose of the database. There are no diagrams or abstract representations; the designer just thinks about the database’s objectives. Some information may be written down, but generally the designer simply considers the best way to organize and use the database.
Next, the designer creates four data models. The conceptual model is a simple diagram that shows table names. After this, the logical data model is created, filling the tables with primary key and information to be collected. A primary key is a title for a column that makes it unique and tells users the purpose of the column. Relationships between tables also are detailed during this database design stage.
In the entity-relationship model, the designer focuses more on relationships and less on the primary keys. This model may sometimes be skipped, but it helps during database creation to show how the entities interact with one another. In the physical data model, live information is fed into the database design.
During each model stage, the spelling of the tables and primary keys must be checked. Naming conventions also are employed, so users know how to enter data. For example, a table could be named “ThisTable,” “This_Table,” “This-Table,” or “This.Table”, based on the naming convention picked by the database designer. Spelling has to be checked, because an error can cause relationship issues when the database is constructed.
The rules of normalization also are applied to the database model. These rules eliminate repeating data, dissolve large tables into small tables and ensure that relationships are optimized. Normalizing the database design is the last step and will aid the designer in determining if the database is functional or if it needs to be rearranged or reworked.