Ten Common Database Design Mistakes. No list of mistakes is ever going to be exhaustive.
People (myself included) do a lot of really stupid things, at times, in the name of “getting it done.” This list simply reflects the database design mistakes that are currently on my mind, or in some cases, constantly on my mind. I have done this topic two times before. If you’re interested in hearing the podcast version, visit Greg Low’s super-excellent SQL Down Under. I also presented a boiled down, ten-minute version at PASS for the Simple-Talk booth.
Before I start with the list, let me be honest for a minute. So, the list: Poor design/planning Ignoring normalization Poor naming standards Lack of documentation One table to hold all domain values Using identity/guid columns as your only key Not using SQL facilities to protect data integrity Not using stored procedures to access data Trying to build generic objects Lack of testing Poor design/planning Ignoring Normalization Are there always 12 payments? Poor naming standards Security. Database Design Guide. Before you set up a new database, usually you spend a lot of time at the white board.
Here are some basic tips: my Dos and Don'ts of database design. Most probably they will reduce your efforts and help you to gain a clean database design. I didn't write the book on database design, but I think my experience earned in many projects could be helpful in some cases. My examples refer to Progress® databases and Progress Software® 4GL, but you'll get the idea, even when you use another database system.
Let's start with a few naming conventions. CamelCase all names. Avoid language mixups, especially if you're not a native speaker and/or your application has no English user interface. Table names and labels designate the business object. Each table can be identified by an (enterprise-wide) unique prefix. So far, so easy. Say you've a table Invoices and a table Addresses: Addresses.AdrOID [primary key] Addresses.AdrOtherAttributes ... Index Invoices.AdrOID and you can code or instead of. Database design. Database design is the process of producing a detailed data model of a database.
This logical data model contains all the needed logical and physical design choices and physical storage parameters needed to generate a design in a data definition language, which can then be used to create a database. A fully attributed data model contains detailed attributes for each entity. The term database design can be used to describe many different parts of the design of an overall database system. Principally, and most correctly, it can be thought of as the logical design of the base data structures used to store the data. In the relational model these are the tables and views. The process of doing database design generally consists of a number of steps which will be carried out by the database designer.
Determine the relationships between the different data elements.Superimpose a logical structure upon the data on the basis of these relationships.[2] ER diagram (entity-relationship model)[edit]