More Common Database Mistakes
Introduction.These are more common mistakes using databases.Primary Key.Use an automatic number as the Primary Identifier Key.For each table, use only one field, as the primary key and linking field, to other tables. This becomes the unique identifier, for every record. It is the only linking field, to other tables. Never use data as the primary key. In Practice.Companies often use a company-created ID, to link relational data together. This is not recommended. Ideally, as a reference, a table linking field never changes.Now, the company has millions of records, in active and history tables to change. Since this ID was used as a linking field, all the data using the ID needs to change. With millions of records, this is no easy task. Add to this all the code referencing these ID's, in different company software. Recommended.Use an automatic number as the primary key, to identify and link. This avoids worrying about the above situation. This saves time, money, and energy.Indexing.Don't over use and don't under-use indexing.Indexing is used for common searches. This includes searches performed by user, or by a program, to link records together. If it is necessary to find customer information quickly, index the fields. This allows quick retrieval of the information. Know the types of information requiring fast retrieval. Think of common, everyday, tasks requiring fast interaction. These are fields to index. Do not index a birthday or salutation field. Index a last name, or date field. Date fields are common query fields for reporting on daily activities. Determine what are the main fields you need to do business on a hour by hour, or minute by minute, basis and index them. Cascading Deletes.Avoid cascading deletes.When setting up referential integrity, be careful. Unless you warn the user of the consequences of deleting, do not use them at all! What if a company has a database using this feature? Suppose a user deletes the top-level lookup record, unwittingly. All the records linked to its status are deleted! This means orders, shipments, payments, everything. There is no undo in a delete. Conclusion.These are more mistakes commonly made when using databases.Xanga Myspace LiveJournal | free pics images | free My space Backrounds |