What makes a well designed database

It requires that all the column references that are not dependent on the primary key should be removed and that no columns in a table should reference to another table except it is a foreign key.

The third normal form requires that there is no duplicate column across related tables. For example, we have an Order table and a Product table. In the Order table we have the product name and then in the Product table too we also have the product name. This is bad design as the ProductID and ProductName columns are being repeated in two tables leading to redundant data. Imagine how bad this would be if there were five to six repeating columns across different tables. A possible solution would be to move the redundant columns to another table, linking both tables with a foreign key.

The need for database design tool in databases cannot be overemphasized. DB Designer makes the task of designing databases easier, ensuring that things can be done correctly.

You must be logged in to post a comment. Good Database Designs: The importance and features. Importance of database design Truth is, when working on small data driven applications, it is easy to just come up with a model and a software implementation almost immediately. When a properly designed database is in place, things become easier. The need for a database design tool Now that the importance of database design has been made clear, it is only reasonable to use available tools to get the best possible results.

First normal form Atomic values in data : A good database design needs to comply with the first normal form. Second normal form Appropriate dependency of data columns The same way a good database has to comply with the first normal form, it also has to comply with the second normal form. Third normal form No redundant data : Finally we would look at the third normal form.

Wrapping it up The need for database design tool in databases cannot be overemphasized. Search for:. Recent Comments. Submit a Comment Cancel reply You must be logged in to post a comment. Get multiple benefits of using own account! Your username is your Emailid Keep your password safe, dont share with others. Login Login in your account..! Username Emailid. Forgot password Create a new account. Create New Don't have an account?

Create New Account Enter Name. Emailid Username. Verification Code. Have an account? Login Here.. Forgot password? Please enter right email to get password! Retrive Password Enter right registered email to receive password!

Enter your emailid Username. Login Here Create a new account. Characteristics of a Well-designed Database System: An ideal database is one that provides fast and easy access to data. The essential features of a well-designed database are explained here: Data integrity Data integrity is said to exist if the data is correct and has no inconsistencies. Data independence Data independence refers to the storage of data in such a way that it is not affected by any modification in the data structure or changes in the application program.

Prevention of data redundancy and inconsistency Data redundancy refers essentially to the storage of data in more than one location. Data sharing and security A well-designed database should permit several users to share the data in the database. Email based Information technology and system assignment help - homework help at Expertsmind Are you searching Computer science expert for help with Characteristics of a Well-designed Database System questions?

In a relational database, you divide your information into separate, subject-based tables. You then use table relationships to bring the information together as needed. Consider this example: the Suppliers and Products tables in the product orders database. A supplier can supply any number of products. It follows that for any supplier represented in the Suppliers table, there can be many products represented in the Products table. The relationship between the Suppliers table and the Products table is, therefore, a one-to-many relationship.

To represent a one-to-many relationship in your database design, take the primary key on the "one" side of the relationship and add it as an additional column or columns to the table on the "many" side of the relationship. In this case, for example, you add the Supplier ID column from the Suppliers table to the Products table.

Access can then use the supplier ID number in the Products table to locate the correct supplier for each product. The Supplier ID column in the Products table is called a foreign key. The Supplier ID column in the Products table is a foreign key because it is also the primary key in the Suppliers table. You provide the basis for joining related tables by establishing pairings of primary keys and foreign keys.

If you are not sure which tables should share a common column, identifying a one-to-many relationship ensures that the two tables involved will, indeed, require a shared column. A single order can include more than one product. On the other hand, a single product can appear on many orders. Therefore, for each record in the Orders table, there can be many records in the Products table.

And for each record in the Products table, there can be many records in the Orders table. This type of relationship is called a many-to-many relationship because for any product, there can be many orders; and for any order, there can be many products.

Note that to detect many-to-many relationships between your tables, it is important that you consider both sides of the relationship. The subjects of the two tables — orders and products — have a many-to-many relationship. This presents a problem. To understand the problem, imagine what would happen if you tried to create the relationship between the two tables by adding the Product ID field to the Orders table.

To have more than one product per order, you need more than one record in the Orders table per order. You would be repeating order information for each row that relates to a single order — resulting in an inefficient design that could lead to inaccurate data. You run into the same problem if you put the Order ID field in the Products table — you would have more than one record in the Products table for each product.

How do you solve this problem? The answer is to create a third table, often called a junction table, that breaks down the many-to-many relationship into two one-to-many relationships. You insert the primary key from each of the two tables into the third table. As a result, the third table records each occurrence or instance of the relationship.

Each record in the Order Details table represents one line item on an order. But together, the two fields always produce a unique value for each record. In the product sales database, the Orders table and the Products table are not related to each other directly. Instead, they are related indirectly through the Order Details table. The many-to-many relationship between orders and products is represented in the database by using two one-to-many relationships:.

The Orders table and Order Details table have a one-to-many relationship. Each order can have more than one line item, but each line item is connected to only one order. The Products table and Order Details table have a one-to-many relationship. Each product can have many line items associated with it, but each line item refers to only one product. From the Order Details table, you can determine all of the products on a particular order. You can also determine all of the orders for a particular product.

After incorporating the Order Details table, the list of tables and fields might look something like this:. Another type of relationship is the one-to-one relationship. For instance, suppose you need to record some special supplementary product information that you will need rarely or that only applies to a few products. Like the Products table, you use the ProductID as the primary key. The relationship between this supplemental table and the Product table is a one-to-one relationship.

For each record in the Product table, there exists a single matching record in the supplemental table. When you do identify such a relationship, both tables must share a common field. When you detect the need for a one-to-one relationship in your database, consider whether you can put the information from the two tables together in one table.

If the two tables have the same subject, you can probably set up the relationship by using the same primary key in both tables. If the two tables have different subjects with different primary keys, choose one of the tables either one and insert its primary key in the other table as a foreign key.

Determining the relationships between tables helps you ensure that you have the right tables and columns. When a one-to-one or one-to-many relationship exists, the tables involved need to share a common column or columns.

When a many-to-many relationship exists, a third table is needed to represent the relationship. Once you have the tables, fields, and relationships you need, you should create and populate your tables with sample data and try working with the information: creating queries, adding new records, and so on.

Doing this helps highlight potential problems — for example, you might need to add a column that you forgot to insert during your design phase, or you may have a table that you should split into two tables to remove duplication. See if you can use the database to get the answers you want. Create rough drafts of your forms and reports and see if they show the data you expect. Look for unnecessary duplication of data and, when you find any, alter your design to eliminate it.

As you try out your initial database, you will probably discover room for improvement. Here are a few things to check for:. Did you forget any columns? If so, does the information belong in the existing tables? If it is information about something else, you may need to create another table.

Create a column for every information item you need to track. Are any columns unnecessary because they can be calculated from existing fields? If an information item can be calculated from other existing columns — a discounted price calculated from the retail price, for example — it is usually better to do just that, and avoid creating new column.

Are you repeatedly entering duplicate information in one of your tables? If so, you probably need to divide the table into two tables that have a one-to-many relationship. Do you have tables with many fields, a limited number of records, and many empty fields in individual records? If so, think about redesigning the table so it has fewer fields and more records. Has each information item been broken into its smallest useful parts? If you need to report, sort, search, or calculate on an item of information, put that item in its own column.

Does each column contain a fact about the table's subject? If a column does not contain information about the table's subject, it belongs in a different table. Are all relationships between tables represented, either by common fields or by a third table? One-to-one and one-to- many relationships require common columns.

Many-to-many relationships require a third table. Suppose that each product in the product sales database falls under a general category, such as beverages, condiments, or seafood. The Products table could include a field that shows the category of each product. Suppose that after examining and refining the design of the database, you decide to store a description of the category along with its name.

If you add a Category Description field to the Products table, you have to repeat each category description for each product that falls under the category — this is not a good solution.

A better solution is to make Categories a new subject for the database to track, with its own table and its own primary key. You can then add the primary key from the Categories table to the Products table as a foreign key. The Categories and Products tables have a one-to-many relationship: a category can include more than one product, but a product can belong to only one category.

When you review your table structures, be on the lookout for repeating groups. For example, consider a table containing the following columns:. Here, each product is a repeating group of columns that differs from the others only by adding a number to the end of the column name. When you see columns numbered this way, you should revisit your design.

Such a design has several flaws. For starters, it forces you to place an upper limit on the number of products. As soon as you exceed that limit, you must add a new group of columns to the table structure, which is a major administrative task.

Another problem is that those suppliers that have fewer than the maximum number of products will waste some space, since the additional columns will be blank. The most serious flaw with such a design is that it makes many tasks difficult to perform, such as sorting or indexing the table by product ID or name. Whenever you see repeating groups review the design closely with an eye on splitting the table in two.

In the above example it is better to use two tables, one for suppliers and one for products, linked by supplier ID. You can apply the data normalization rules sometimes just called normalization rules as the next step in your design. You use these rules to see if your tables are structured correctly. The process of applying the rules to your database design is called normalizing the database, or just normalization. Normalization is most useful after you have represented all of the information items and have arrived at a preliminary design.

The idea is to help you ensure that you have divided your information items into the appropriate tables. What normalization cannot do is ensure that you have all the correct data items to begin with. You apply the rules in succession, at each step ensuring that your design arrives at one of what is known as the "normal forms.

This article expands on the first three, because they are all that is required for the majority of database designs.