SQL Database Design » 6. Database Modeling Process

6. Database Modeling Process

Designing databases is a top-down process, implementing them is bottom-up. It's difficult to determine when design ends and when implementation starts. In database design, selecting specific data types and defining constraints already involves a concrete implementation tasks, but they can also be considered low-level design, depending on your interpretation.

The SQL standard enables developers to create logical models, that don't necessarily need much or any translation to a specific DBMS. Physical specifics are reduced to such a minimum that creating separate physical models isn't really necessary. While old-fashioned developers and/or purists still expect to use domains, using them is no longer required. According to Matthiessen, domains didn't prevail in practice anyway. Thanks to the SQL standard, most DBMSs implement a common set of predefined data types, which allows developers to easily switch between database products.

Depending on your approach, you either have a conceptual model that you wish to translate to a logical model or you skip conceptual models entirely, starting with the definition of tables and foreign keys right away. Often, developers don't feel the need for conceptual models (like myself). Many database design tools, especially the DBMS-specific ones, don't offer conceptual-level modeling. Instead, they make use of what the SQL standard defines and include additional functionality, for example to connect to a live database to synchronize with your model (MySQL Workbench, Oracle SQL Developer, ...).

Modeling databases is basically a four-fold task: first, you determine distinct entity types and create a table for each. Next, you determine the primary key properties, for which you must choose appropriate data types. Then, mostly concurrently, you model all other attributes that come to your mind or they are already documented in a conceptual model. Last but not least, you need to add constraints, which quickly becomes a major challenge, especially when foreign keys come into play.

These tasks are the core of database design. The following sections should give you sufficient advice to create mostly DBMS-independent, logical models with only a minimum of DBMS-specifics, which, as you will see, can hardly be avoided.

Last updated: 2010-10-13