Part 2 - Design Tables and Fields 1. Check that various changes I will consider which data fields will change in the future when designing the database. For example, the surname is this (note is the surname of Westerners, such as women who have married after marriage). So, when establishing a system storage customer information, I tend to store the last name field in a separate data table, but also attach the starting day and terminating day, so that this data entry can be tracked. - Shropshire lad2. With meaningful field name, I have participated in the development of a project. There are programs that have been inherited from other programmers. The programmer likes to display the data indicator to name the field name in the screen, which is not bad, but unfortunate It is, she also likes to use some strange nomenclasses, which naming the combination of Hungarian naming and control serial numbers, such as CBO1, TXT2, TXT2_B, and the like. Unless you are using a system that is only to your abbreviated field name, please clearly describe the fields as well as possible. Of course, don't do your head, such as Customer_Shipping_Address_Street_Line_1 I, although it is clear, no one is willing to type such a long name, the specific scale is in your grasp. - Lamont Adams3. Adopt prefix naming If you have a lot of fields (such as firstname), you may wish to help you identify fields with a particular table prefix (such as cuslastname). - NOTORIOUSDOG timeliness data should include the "Recent Update Date / Time" field. Time markers For the reasons for finding data issues, rescreasting / overloading data by date and clears the old data is particularly useful. - Kol5. Standardization of standardization and data-driven data is not only convenient and convenient for others. For example, if your user interface is to access external data sources (files, XML documents, other databases, etc.), you might as well as store the corresponding connection and path information in the user interface support table. Also, if the user interface performs tasks such as workflow (send mail, print letter, modifying record status, etc.), then data generating workflow can also be stored in the database. The pre-arrangement will always work hard, but if these processes use data drivers rather than hard coding, then strategy changes and maintenance will be much easier. In fact, if the process is data-driven, you can push considerable responsibility to the user, by the user to maintain its workflow process. - TDUVALL6. Standardization can not live for those who are not familiar with standardization (Normalization), standardization can ensure that the fields in the table are the most basic elements, and this measure will help eliminate data redundancy in the database. Standardization has several forms, but Third Normalform (3NF) is often considered to have the best balance in performance, scalability, and data integrity. Simply, 3NF regulations:
· Each value in the table can only be expressed once. · Each line in the table should be unique (with unique keys). • Non-bond information dependent on other keys should not be stored within the table. Compliance with 3NF standards has the following features: There is a set of tables that store the associated data connected by the keys. For example, a 3NF database that stores customers and its related orders may have two tables: Customer and Order. The ORDER table does not contain any information for the order related to the customer, but a key value will be stored in the table, which points to the line of the customer information in the Customer table. Higher levels of standardization are also available, but it is better to be better? The answer is not necessarily. In fact, for some projects, even 3nf may introduce too high complexity to the database. - Lamont Adams For the sake of efficiency, it is also necessary to standardize the table, so many examples. There have been a development of financial analysis software to use a non-standardized table to reduce the query time from an average of 40 seconds to two seconds. Although I have to do this, I will never use the non-standardized design concept of the data sheet as the design concept. The specific operation is just a derived. So if you anticipate non-standardized tables, it is entirely possible. - Epepke7. Microsoft Access Report Skills If you are using Microsoft Access, you can use the name of the user-friendly field name: For example, use Customer Name instead of TXTCNAM. This way, when you create a form and report with a wizard, its name will make people who are not programmers easier to read. - jwoodruf8. Inactive or unused indicators add a field indicating whether the record is no longer active in the business. Whether it is a customer, anyone else is still otherwise, so that it can help to filter active or not active when running inquiry. At the same time, it also eliminates some of the problems faced when using data, for example, some records may no longer be used for them, and then delete it. - Theoden9. Using role entity Definitions belonging to a certain category When you need to define a particular category or a specific role, you can create a specific time-related relationship with a role entity, so you can achieve self-documentation. The meaning here is not to let the Person entity comes with a title field, but said why don't you describe the person with a Person entity and a person_type entity? Then, when John Smith, Engineer is upgraded to Johnsmith, Director and even finally climbed to John Smith, CIO's high, and all you have to do is changing the key value between the two tableson and person_type, while increasing A date / time field to know when the change occurs. In this way, your Person_Type table contains all Person possible types, such as Associate, Engineer, Director, CIO or CEO, etc. There is also an alternative to changing the Person record to reflect changes in the new title, but this cannot track the specific time of the position where you are in place. 10. The easiest way to adopt commonly used entity nomenclature data organization data is to adopt common names, such as: Person, Organization, Address, and Phone, etc. When you combine these commonly used general names or create a specific corresponding copile body, you get your own special versions. The main reason for adopting general terms at the beginning is that all specific users can be embodied in abstract things. With these abstract representations, you can use your own special names in the 2nd level, for example, Person may be Employee, Spouse, Patient, Client, Customer, Vendor or Teacher, etc.
Similarly, Organization may also be Mycompany, MyDepartment, Competitor, Hospital, Warehouse, Government, etc. Final Address can be specifically Site, Location, Home, Work, Client, Vendor, Corporate, and FieldOffice. Using general abstract terms to identify "things" categories allow you to achieve huge flexibility in association with business requirements, while doing so can significantly reduce the redundancy required for data storage. - Teburlew11. When designing a database in the world is designed to a network or other international characteristics, it is necessary to remember that most countries have different field formats, such as postal coding, etc., some countries, such as New Zealand, there is no postal code. Say. - BILLH12. Data Repeat requires the discrete data sheet if you find yourself in repeated input data, please create a new table and new relationship. - Alan Rash13. 3 useful fields · DRECORDCREATIONDATE you should add in each table, default is now (), and getDDATE () · SRecordcreator under SQL Server, under SQL Server, NOT NULL DEFAULT USER · NRECORDVERSION, recorded version tags; helps to accurately describe NULL data or loss data in records - Peter Ritchie14. It is not enough to use multiple fields to use multiple fields for addresses and phones. Address_Line1, address_line2 and address_line3 can provide greater flexibility. Also, the phone number and email address are best to have its own data sheet, and it has its own type and tag category. - DWNERD Over-standardization can be careful, doing so may result in problems in performance. Although address and telephone tables can usually reach the best, if you need to access such information frequently, you may be more appropriate to store "preferred" information (such as Customer et al.) In its parent table. Compromise between non-standardized and accelerated access is certain. - DHATTREM15. I feel very surprised to use multiple names fields, and many people have a field to Name in the database. I think that only developers who have just been introduced will do this, but this practice is actually very common online. I suggest that the last name and name are treated as two fields, and then combine them again when querying. - Klempanklempan is not the only person noticed to use a single Name field to make this situation more friendly to users. I am most commonly created in the same table, which can be used automatically by automatically connecting the standardized field so that it will change when data changes. However, doing so when using modeling software. In summary, use the connection field to effectively isolate the user application and developer interface. - Damon16. Bewix the size of the object name and special characters in the past, one of the most annoyed things in the past is the object name in the database, such as CustomerData. This problem exists from Access to the Oracle database. I don't like to use this case-sensitive object naming method, and the result has to be manually modified. Think about it, can this database / application not to use a more powerful database? All uppercases and the names containing the names of the underwriting have better readability (Customer_Data), absolutely don't leave space between characters of the object name. - BFREN17. Be careful to keep your field name without a retention word, database system or common access method conflict, for example, there is a table in an ODBC connection program I have written, where DESC is used as a description field name. The consequences can imagine! DESC is the reserved word after the abbreviation of Descending.
A SELECT * statement in the table is available, but I get it is a lot of information that is useless. - Daniel Jordan18. Keep the field name and type consistency must guarantee consistency when named the field and specify the data type. If the field is called "agreement_number" in a table, you don't change the name to "REF1" in another table. If the data type is an integer in a table, you can turn it in another table. Remember, you have done your own lives, others have to use your database. - setanta19. Carefully select Digital Types Use Smallint and Tinyint type in SQL to be particularly careful, for example, if you want to see the total monthly sales, your total field type is smallint, then if the total amount exceeds $ 32,767 You can't calculate Operate. - Egermain20. Deleting tag contains a "Delete Tag" field in the table so that the row is marked as deletion. Do not delete a row separately in the relational database; it is best to use clear data programs and carefully maintain index integral. - KOL21. The function avoiding the use of trigger triggers can usually be implemented in other ways. The trigger may become interference when debugging the program. If you really need a trigger, you'd better focus on its documentation. - KOL22. Contains version mechanism recommends that you introduce version control mechanisms in the database to determine the version of the database in use. This requires this requirement anyway. For a long time, the needs of users will always change. It may eventually be required to modify the database structure. Although you can determine the version of the database structure by checking new fields or claims, I found that the version information is not more convenient in the database? . - Richard Foster23. For text fields, pay the balance ID type text field, such as customer ID or order number, etc. should be set to be bigger than the general imagination, because time is not long, you will have additional characters It is unpleasant. For example, suppose your customer ID is 10 digits. Then you should set the length of the database table field to 12 or 13 characters. Is this a waste of space? There is a little, but there is so much that you imagine: A field extends 3 characters in 1 million records, plus a little index, but the entire database is more than 3MB of space. But this additional space can achieve the growth of database size without the need to refactor the entire database in the future. - TLUNDIN24. Column name tips We found that if you give a unified prefix if you give each table, you will be greatly simplified when writing SQL expressions. This does have something disadvantaged, such as destroying the role of the automatic table connection tool, the latter links the public column name to some databases, but even these tools sometimes do not connect to errors. For a simple example, assume that there are two tables: Customer and Order. The prefix of the Customer table is CU_, so the subparaminated in the table is as follows: Cu_name_ID, Cu_Initials, and Cu_Address, etc. The prefix of the ORDER table is OR_, so the subparade name is: or_order_id, or_cust_name_id, or_quantity, and or_description, etc.
