Proper database design is always important, whether you’re building a large database with millions of records or a small database with a few hundred records. The proper database design allows you to find the information you need quickly and efficiently while also making it easy to expand your database in the future. However, there are a few easy design mistakes that can hurt your database.
Here are five database design errors to avoid and why.
1. Adding More than One Information Piece into a Field
Each field should contain one piece of information. If you add more than this, it’s going to be much harder to query the database for information.
When creating a database, be sure that all fields contain only piece of information. We also recommend watching the formatting for each field. For example, people input phone numbers differently, so if you rely on a specific format, a query may not pull up all of the information you need.
2. Choosing a Poor Primary Key
Primary keys should never change. Usually, people assign incrementing numbers as primary keys because they are automatically generated, unique and non-changing.
You don’t want to use things like addresses, phone numbers or social security numbers as primary keys. Also, it’s not recommended to use real information as your primary key, even if it appears to be a good identifier. Keep it simple – incrementing numbers are best.
3. Repeating Fields in a Table
When designing your database, you should recognize repeating data and put the repeating columns in their own table. Otherwise, you might stuff repetitive data into a single table, making it difficult to run accurate reports. Usually, this problem happens when going from spreadsheets to databases, but databases are relational and don’t need repetitive data.
4. Embedding a Table in a Table
Another database design mistake to avoid is embedding a table in a table. All data in a table should be related to itself. When the data is related, it pertains to that individual person or order, making it easy to keep the information updated and accurate.
If you embed a table, you will have to update a whole set of data when one piece of information changes. You want to avoid this and also allow for new information to be added quickly and easily.
5. Improper Indexing
Indexing can be a difficult thing to do right, but it must be done, otherwise you won’t see the full potential from your database. All primary and foreign keys should be indexed because this is what links tables together.
You should also index other fields, such as “where” fields, because you might want to search for information related to “where.” While it’s a good idea to put an index on commonly used fields, don’t overdo it. Again, we suggest placing an index on all primary keys, all foreign keys and fields that are used in “where” clauses.
Good database design is the key to running accurate reports. To ensure that your database is set up correctly, or to have a new database designed for your organization, contact Arkware today at 877-519-4537.