How to Build a Relational Database
Understanding the Scope
- 1). Review the key concepts of relational databases. Understand the definitions of tables, primary keys and foreign keys. Also review the relationship types that can exist between tables, which are one-to-one (1:1), one-to-many (1:M) and many-to-many (M:M).
- 2). Determine who will use the database, and discuss with those people how often they will use it and what kind of data they must store. Understanding this will help determine size, security needs and volume of transactions that the database must be capable of supporting.
- 3). Discuss the software requirements of the application with the application developers. Technical requirements and constraints may exist that you must take into consideration before you can begin designing.
Designing the Database
- 1). Sketch out the major objects as tables showing how the objects relate to one another. For example, a customer database may need to store three objects as tables: Customers, Order History and Returns. In the case of Customers, a customer may have one or many orders, so the relationship between the tables must support a 1:M relationship. In contrast, the relationship between Order History and Returns might be M:M, because a given order may have several returns and a return may be linked to several orders. Build your tables and relationships to support the business rules.
- 2). Determine key columns for each table. Once you understand the objects and relationships, determine the columns that uniquely identify records in a table, which are called primary keys. These might be an automatically generated column or customer data, such as social security number or phone number. Establish primary keys for each table that will be in the database regardless of its relationship to another table.
- 3). Document additional columns needed for each table. You can do this in a spreadsheet application rather than a data-modeling tool, if necessary. Be sure to record the length of each column and the type of data that will be stored. Also, add any foreign keys necessary to link tables. Foreign keys are columns that store the primary key of a row from another table in order to link the tables.
Building and Testing the Database
- 1). Review the conceptual design with users and programmers. Explain in simple language how each table is linked and whether the relationship is 1:1, 1:M or M:M. Ask questions that challenge the design's integrity, such as, "Is it ever possible for an order to have two different customers?" to ensure the tables can meet desired goals.
- 2). Create the physical database. If you are using modeling software to design the database, many of these programs have the ability to create the database directly from the design. Alternatively, you can build the database manually, by first creating the database, then the tables and finally the columns.
- 3). Test the database to ensure it is storing the data properly. Also, ensure that users can query data and that tables linked together as intended.