Lesson 8 Database Logical & Physical Design

Database design can be divided into two portions, Logical Design and physical design. Logical design is nothing but understanding the business, business logics, business rules and then converting those logics to tables, columns, constraints, rules, keys, SPs, views etc.

You can understand Logical design in simple words, like if you are creating a new database for a school, then Roll Number, Student Name, Standard, Address are good to have columns in Student_Record table.

Physical database design, on the other hand, involves mapping the logical design onto physical media, taking advantage of the hardware and software features available (or simply RDBMS or any other database tool), which allows the data to be physically accessed and maintained as quickly as possible, and indexing. In simple words, physical database design is nothing but designing your physical data storage plan, decisions for storing data files on SAN or local disk, which drive, how many data files or file groups etc.

Bad logical database design results in bad physical database design as well which generally leads to poor database performance. So, if it is DBA’s responsibility to design a database from scratch, spend good amount of time and take the necessary steps and efforts to get the logical database design right. Once the logical design is right, then you also need to take the time to get the physical design right.

Both the logical and physical design must be right before you can expect to get good performance out of your database. If the logical design is not right before you begin the development of your application, it is too late after the application has been implemented to fix it. No amount of fast, expensive hardware can fix the poor performance caused by poor logical database design