Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the methods of database design". The content of the explanation is simple and clear, and it is easy to learn and understand. let's follow the editor's train of thought. Let's study and learn "what are the methods of database design"?

Database design skills:

Part 2-Design tables and fields

1. Check for changes

When I design the database, I consider which data fields may change in the future. For example, the last name is like this. (note

It means the surname of a Westerner, such as a woman taking her husband's surname after marriage. So, when setting up a system to store customer information, I tend to

The last name field is stored in a separate data table, and fields such as the start date and end date are appended so that this can be tracked

Changes in data entries.

-Shropshire Lad

two。 Use meaningful field names

Once I worked on a project in which there were programs inherited from other programmers who liked to use the on-screen

Displaying data indicates naming fields, which is not bad, but unfortunately, she also likes to use some strange naming methods.

Use a combination of Hungarian naming and control serial numbers, such as cbo1, txt2, txt2_b, and so on.

Unless you are using a system that only targets your abbreviated field name, please describe the field as clearly as possible. Of course, don't.

Go too far, for example, Customer_Shipping_Address_Street_Line_1 I is very illustrative, but no one wants to

Type such a long name, and the exact yardstick is in your grasp.

-Lamont Adams

3. Named with prefix

If there are many fields of the same type in multiple tables (such as FirstName), you might as well use the prefix of a specific table (such as

CusLastName) to help you identify fields.

-notoriousDOG

Timeliness data should include the Last Updated date / time field. Time stamp to find the cause of the data problem and relocate it by date

Managing / reloading data and purging old data are particularly useful.

-kol

5. Standardization and data-driven

The standardization of data is convenient not only for yourself but also for others. For example, if your user interface wants to access external data,

Source (files, XML documents, other databases, etc.), you might as well store the corresponding connection and path information in the user interface support table

In. Also, if the user interface performs tasks such as sending mail, printing letterhead, changing the status of records, etc., then

The data that generates the workflow can also be stored in the database. It always takes effort to arrange in advance, but if these processes use a number of

Policy changes and maintenance are much easier if they are driven rather than hard-coded. In fact, if the process is data-driven

You can shift a considerable amount of responsibility to the users, who maintain their own workflow process.

-tduvall

6. Standardization should not go too far

For those who are not familiar with the word normalization, standardization ensures that the fields in the table are basic.

Elements, and this measure helps to eliminate data redundancy in the database. There are several forms of standardization, but Third Normal

Form (3NF) is generally considered to achieve the best balance in terms of performance, scalability, and data integrity. To put it simply, the 3NF regulation

Set:

Each value in the table can only be expressed once.

Each row in the table should be uniquely identified (with a unique key).

Non-key information that depends on other keys should not be stored in the table.

Databases that comply with 3NF standards have the following characteristics: there is a set of tables dedicated to storing associated data connected by keys. For example,

A 3NF database that holds customers and their related orders may have two tables: Customer and Order. Order table is not included.

Contains any information about the customer associated with the order, but there is a key value in the table that points to the customer information in the Customer table

That line of work.

There is also a higher level of standardization, but is it necessarily better if it is more standard? The answer is not necessarily. In fact, for some projects,

Even 3NF can introduce too much complexity to the database.

-Lamont Adams

There are many examples that it is sometimes necessary not to standardize tables for the sake of efficiency. Once upon a time, there was a development financial analysis

The job of the software is to use non-standardized tables to reduce the query time from an average of 40 seconds to about two seconds. Even though I have to do it.

But I will never take the non-standardization of datasheets as a natural design concept. And the specific operation is just a derivative. So if the table

It is entirely possible to recreate a non-standardized table if something goes wrong.

-epepke

7. Microsoft Access reporting skills

If you are using Microsoft Access, you can replace the numbered name with a user-friendly field name: for example, with

Customer Name replaces txtCNaM. In this way, when you use the wizard to create forms and reports, their names will make those who do not

People who are programmers are easier to read.

-jwoodruf

8. Indicators that are inactive or not used

It is useful to add a field to indicate whether the record is no longer active in the business. Whether it's customers, employees or anything else.

People, doing so can help filter active or inactive states when you run the query again. At the same time, it also eliminates the adoption of new users

Some problems faced by the data, for example, some records may no longer be used by them, and can play a certain role when they are deleted.

Preventive effect.

-theoden

9. Use role entities to define columns that belong to a category

When you need to define things that belong to a particular category or have a specific role, you can use role entities to create specific timelines.

Connect with each other, so that you can achieve self-documentation.

The meaning here is not to let the PERSON entity have a Title field, but rather, why not use the PERSON entity and

What about PERSON_TYPE entities to describe people? And then, for example, when John Smith, Engineer is promoted to John

Smith, Director and finally climb to the top of John Smith CIO, and all you have to do is change the two watches

The key value of the relationship between PERSON and PERSON_TYPE, while adding a date / time field to know when the change is

It happened. In this way, your PERSON_TYPE table contains all the possible types of PERSON, such as Associate,

Engineer, Director, CIO or CEO, etc.

Another alternative is to change the PERSON record to reflect the change in the new title, but it is untraceable in terms of time

The specific time of an individual's location.

-teburlew

10. Use common entities to name organization data

The easiest way to organize data is to use common names such as PERSON, ORGANIZATION, ADDRESS, and

PHONE, wait. When you combine these commonly used general names or create specific corresponding sub-entities, you get

A special version for your own use. The main reason for using general terms at the beginning is that all concrete users are able to deal with abstract things.

Assimilation.

With these abstract representations, you can use your own special name in the level 2 identity, for example, PERSON may be

Employee, Spouse, Patient, Client, Customer, Vendor or Teacher, etc. The same

ORGANIZATION may also be MyCompany, MyDepartment, Competitor, Hospital,

Warehouse, Government, etc. Finally, ADDRESS can be Site, Location, Home, Work,

Client, Vendor, Corporate and FieldOffice, etc.

Using general abstract terms to identify categories of "things" can give you great intelligence in associating data to meet business requirements.

Active, and doing so can also significantly reduce the amount of redundancy required for data storage.

-teburlew

11. Users come from all over the world

When designing networks or databases with other international characteristics, it is important to keep in mind that most countries have different field boxes

Some countries, such as New Zealand, do not have a zip code.

-billh

twelve。 Data duplication requires separate data tables

If you find yourself re-entering data, create a new table and a new relationship.

-Alan Rash

13. Three useful fields that should be added to each table

DRecordCreationDate, which defaults to Now () under VB and GETDATE () under SQL Server

SRecordCreator, defaults to NOT NULL DEFAULT USER under SQL Server

NRecordVersion, the version mark of the record; helps to accurately indicate the origin of the null data or missing data in the record

Because of

-Peter Ritchie

14. Use multiple fields for addresses and phones

It is not enough to describe the street address with just one line of record. Address_Line1, Address_Line2, and Address_Line3 can

To provide greater flexibility. Also, phone numbers and e-mail addresses are best to have their own data sheets with their own types

And tag categories.

-dwnerd

Be careful with excessive standardization, which can lead to performance problems. Although the separation of address and phone list can usually be achieved.

At its best, but if you need to access this information frequently, you may store "preferred" information in its parent table (such as

Customer, etc.) is more appropriate. The compromise between non-standardization and accelerated access makes sense.

-dhattrem

15. Use multiple name fields

I was surprised that many people left a field for name in the database. I think only beginner developers will do this.

What to do, but in fact this practice is very common on the Internet. I suggest that the last name and first name should be treated as two fields, and then in the

Put them together when you query.

-klempan

Klempan is not the only one who has noticed the use of a single name field to make this situation more user-friendly.

Some methods. What I most often use is to create a computed column in the same table, through which standardized fields can be automatically joined, which

When the sample data changes, it also changes. However, this has to be very clever when using modeling software. In short, use the connection

Fields can effectively isolate the user application from the developer interface.

-damon

16. Beware of mixed case object names and special characters

One of the things that annoyed me most in the past was that there were mixed case object names in the database, such as CustomerData. This question

Questions range from Access to Oracle databases. I don't like to use this kind of mixed case object naming method, and it turns out not yet.

You don't have to change the name by hand. Think about it, can this kind of database / application mix to the day when a more powerful database is adopted? Adopt full

Names that are uppercase and contain underscores have better readability (CUSTOMER_DATA), and should never be specified in the

Leave spaces between characters.

-bfren

17. Be careful to keep words

Make sure that your field name does not conflict with reserved words, database systems, or common access methods, such as the one I recently wrote

There is a table in the ODBC linker in which DESC is used as the description field name. You can imagine the consequences! DESC is

Reserved word after DESCENDING acronym. A SELECT * statement in the table works, but I get a lot of it.

Useless information.

-Daniel Jordan

18. Maintain consistency of field names and types

Be sure to ensure consistency when naming fields and assigning them data types. If the field is called in a table

"agreement_number", don't change your name to "ref1" in another table. If the data type is in a table

It's an integer, so don't turn it into a character in another table. Remember, you've done your job, and other people have to use your numbers.

According to the library.

-setanta

19. Choose the number type carefully

Be very careful when using the smallint and tinyint types in SQL, for example, if you want to look at the total monthly sales, your total words

The segment type is smallint, so you cannot calculate if the total exceeds $32767.

-egermain

20. Delete tag

Include a Delete tag field in the table so that rows can be marked for deletion. Do not delete separately in a relational database

A row; it is best to use a purge data program and carefully maintain the integrity of the index.

-kol

21. Avoid using triggers

The function of triggers can usually be implemented in other ways. Triggers can become interference when debugging a program. If you really need to pick

With a trigger, you'd better focus on documenting it.

-kol

twenty-two。 Include version mechanism

It is recommended that you introduce a version control mechanism into the database to determine the version of the database in use. You have to achieve this no matter what.

Please. Over time, the needs of users will always change. Eventually, the database structure may be required to be modified. Although you can pass the test.

Look up new fields or indexes to determine the version of the database structure, but I find it not more convenient to store the version information directly in the database

Is it convenient?

-Richard Foster

23. Leave enough room for the text field

Text fields of ID type, such as customer ID or order number, etc., should be set larger than usual, because it won't take long for you

It is likely to be embarrassed to add extra characters. For example, suppose your client's ID is 10 digits long. Then you should

Set the length of the database table field to 12 or 13 characters long. Is this a waste of space? A little bit, but it's not what you think.

That much: a field lengthened by three characters with 1 million records and a little index is only enough to make the whole data

Most of the libraries take up the space of 3MB. But this extra space can realize the scale of the database without having to reconstruct the entire database in the future.

It's growing.

-tlundin

24. Column naming skills

We found that if you use a uniform prefix for the column names of each table, you will get large when writing SQL expressions.

Big simplification. This does have drawbacks, such as undermining the function of the automatic table join tool, which equates public column names with certain numbers

It is linked according to the library, but even these tools are sometimes connected incorrectly. To take a simple example, suppose there are two tables:

Customer and Order. The prefix of the Customer table is cu_, so the subsegment names in the table are as follows: cu_name_id,

Cu_surname, cu_initials and cu_address, etc. The prefix of the Order table is or_, so the subsegment name is:

Or_order_id, or_cust_name_id, or_quantity and or_description, etc.

So the SQL statement that selects all the data from the database can be written as follows:

Select * from Customer, Order

Where cu_surname = "MYNAME"

And cu_name_id = or_cust_name_id

And or_quantity = 1

In the absence of these prefixes, it looks like this:

Select * from Customer, Order

Where Customer.surname = "MYNAME"

And Customer.name_id = Order.cust_name_id

And Order.quantity = 1

The first SQL statement does not type many characters less. But if the query involves five tables or more columns, you know this technique.

How useful.

-Bryce Stenberg

Part 3-Select keys and indexes

1. Data mining should be planned in advance

At one point, my marketing department had to handle more than 80,000 contact information and fill in the necessary data for each customer (this is definitely not small.

Live). I also have to identify a group of customers as market targets. When I designed tables and fields from the beginning, I tried not to

Add too many fields to the main index to speed up the database. And then I realized that specific group queries and information mining

Neither accurate nor fast. As a result, the data fields are rebuilt and merged in the primary index. I found that there was an instruction plan phase.

When the key-why should I use the number as the primary index field when I want to create a system type lookup? I can use the fax number.

For retrieval, but it's almost as unimportant to me as the system type. The latter is used as the main field to update the database.

It is much faster to re-index and retrieve later.

-hscovell

There are differences in data indexing between operational data warehouse (ODS) and data warehouse (DW). In the DW environment

Next, you should consider how the sales department organizes sales activities. They are not database administrators, but they make sure that the

Key information. Here the designer or database staff should analyze the database structure to determine the relationship between performance and correct output

The best conditions.

-teburlew

two。 Use a system-generated primary key

This day is the same as Tip 1, but I think it is necessary to remind you here. If you always use it when designing a database

If the key generated by the system is used as the primary key, then you actually control the index integrity of the database. In this way, databases and non-manual mechanisms

Access to each row of stored data is effectively controlled.

Using system-generated keys as primary keys has another advantage: when you have a consistent key structure, it's easy to find logical flaws.

-teburlew

3. Decompose fields for indexing

To separate named fields from containing fields to support user-defined reports, consider decomposing other fields (or even primary keys) into their groups

Make a feature so that users can index it. Indexing speeds up the execution of SQL and report builder scripts. For example,

I usually create reports where I have to use SQL LIKE expressions, because case number fields cannot be decomposed into

Elements such as year, serial number, case type and defendant code. The performance will also deteriorate. If the year and type words

Segments can be broken down into index fields so that these reports run much faster.

-rdelval

4. Four principles of key design

Create a foreign key for the associated field.

All keys must be unique.

Avoid using compound keys.

Foreign keys are always associated with unique key fields.

-Peter Ritchie

5. Don't forget to index.

Indexing is one of the most efficient ways to get data from a database. 95% of the database performance problems can be obtained by index technology.

Solve it. As a rule, I usually use a unique grouped index for logical primary keys and system keys (as stored procedures)

A unique non-grouped index that uses a non-grouped index on any foreign key column. However, the index is like salt, too many dishes will be too bad. you

Consider how much space the database has, how the tables are accessed, and whether these accesses are used primarily for reading and writing.

-tduvall

Most databases index automatically created primary key fields, but don't forget to index foreign keys, which are also frequently used than

It is useful if you run a query that shows a record of the main table and all associated tables. Also, do not index the memo/note field, no

To index large fields (with many characters), doing so will make the index take up too much storage space.

-gbrayton

6. Do not index commonly used small tables

Do not set any keys for small data tables, especially if they often have insert and delete operations. For these inserts and

Index maintenance for delete operations may take more time than scanning tablespaces.

-kbpatel

7. Do not select the social security number (SSN) as the key

Never use SSN as the key to a database. Apart from privacy reasons, it should be noted that the government is increasingly inclined to disallow

SSN is used for purposes other than revenue related, and SSN needs to be entered manually. Never use manually typed keys as primary

Key, because if you type a mistake, the only thing you can do is delete the entire record and start from scratch.

-teburlew

When I was in college in the 1970s, I remember that SSN was used as a student number, of course, despite the right and wrong.

It's legal. And people know it's illegal, but they're used to it. Later, with the increase in the crime of identity theft

Plus, my current college campus is painfully removing SSN from a large stall of data.

-generalist

8. Do not use the user's key

When determining which field to use as the key for the table, be sure to be careful about the field the user is going to edit. Not under normal circumstances.

Select a field that the user can edit as the key. Doing so will force you to take the following two steps:

Impose restrictions on the user's behavior of editing fields after the record is created. If you do this, you may find your application

The order lacks sufficient flexibility when business requirements suddenly change and users need to edit fields that are not editable. When used

After entering the data, users didn't find out that there was something wrong with the system until they saved the record. what do they think? Delete and rebuild? If the record is not available

Does the rebuild make the user walk away?

Some methods to detect and correct key conflicts are proposed. Usually, it takes a little effort to get it done, but in terms of performance,

The price will be higher. Also, key correction may force you to break through the gap between your data and the business / user interface layer

Away from.

So repeat the old saying: your design should adapt to the user, not let the user adapt to your design.

-Lamont Adams

The reason why primary keys are not updatable is that in relational mode, primary keys implement associations between different tables. such as,

The Customer table has a primary key CustomerID, while the customer's order is stored in another table. The primary key of the Order table may be

It is a combination of OrderNo or OrderNo, CustomerID and date. No matter which key setting you choose, you need to set it in the

The CustomerID is stored in the Order table to ensure that you can find the order record for the user who placed the order.

If you modify the CustomerID in the Customer table, then you must find all the relevant records in the Order table to enter it.

Row modification. Otherwise, some orders will not belong to any customer-the integrity of the database will be ruined.

If index integrity rules are imposed at the table level, it is almost impossible without writing a lot of code and additional deletion records

Change the key of a record and all associated records in the database. And this process is often full of mistakes, so it should be avoided as much as possible.

-ljboast

9. Optional keys can sometimes be used as primary keys

Remember, it is not machines but people who query data.

If you have an optional key, you may further use it as the primary key. In that case, you have the ability to build a strong index. this

Samples can prevent people who use the database from having to connect to the database to filter the data properly. In strictly controlling the database of domain tables

This kind of load is more eye-catching. If the optional key is really useful, it is at the level of the primary key.

In my opinion, if you have an optional key, such as state_code in the country table, don't put it on the only key that you can't change.

Create subsequent keys. All you have to do is create worthless data. For example, the following example:

Select count (*)

From address, state_ref

Where

Address.state_id = state_ref.state_id

And state_ref.state_code = 'TN'

Here's what I'm doing:

Select count (*)

From address

Where

And state_code = 'TN'

If you overuse the subsequent keys of the table to establish this kind of table association, the operation load really needs to be considered.

-Stocker

10. Don't forget the foreign key.

The primary key field that is automatically created by most database indexes. But don't forget to index the foreign key fields, which are the records in the main table you want to query.

And its associated records are used every time. Also, do not index memo/notes fields and do not index large text fields

(many characters), doing so will make your index take up a lot of database space.

-gbrayton

Part 4-ensuring the integrity of data

1. Enforce data integrity with constraints rather than business rules

If you deal with requirements according to business rules, you should check the business level / user interface: if the business rules change later

Then you only need to update it.

If the requirements arise from the need to maintain data integrity, then restrictions need to be imposed at the database level.

If you do use constraints in the data layer, you need to make sure that there is a way for users to understand why updates fail constraint checking

Notifies the user interface in the language of Unless your field name is lengthy, the field name itself is not enough. -Lamont Adams

Whenever possible, use a database system to achieve data integrity. This includes not only the integrity achieved through standardization but also

Including the functionality of the data. Triggers can also be added when writing data to ensure the correctness of the data. Don't rely on the business layer

Ensures data integrity; it does not guarantee the integrity between tables (foreign keys) and therefore cannot be imposed on other integrity rules.

-Peter Ritchie

two。 Distributed data system

For distributed systems, before you decide whether to copy all the data at each site or keep the data in one place

Estimate the amount of data in the next 5 or 10 years. When you transfer data to other sites, it is best to use the database field

Set some tags in the. Update your tags after your data is received at the destination site. For this kind of data transmission, please write down

Your own batch or scheduler runs at specific intervals without letting users transfer data after daily work. Local

Copy your maintenance data, such as calculating constants and interest rates, and set the version number to ensure that the data is exactly the same at each site.

-Suhair TechRepublic

3. Force indication integrity

There is no good way to eliminate harmful data after it enters the database, so you should remove it before it enters the database. Excited

Indicative integrity characteristics of live database systems. This keeps the data clean and forces developers to invest more time

Manage the wrong conditions.

-kol

4. Relationship

If there is a many-to-one relationship between two entities, and it is possible to turn into a many-to-many relationship, you'd better set it at the beginning.

Into a many-to-many relationship. It is much more difficult to move from an existing many-to-one relationship to a many-to-many relationship than to a many-to-many relationship from the beginning.

-CS Data Architect

5. Adopt view

To provide another layer of abstraction between your database and your application code, you can create a dedicated

View without requiring the application to access the data table directly. This also provides you with more information when dealing with database changes

free.

-Gay Howe

6. Plan for data retention and recovery

Consider the data retention strategy and include it in the design process and design your data recovery process in advance. Adoption can be released to users / developers

The personnel's data dictionary realizes convenient data recognition and ensures the documentation of the data source. Write online updates to "update queries" for

Updates can be reprocessed in case of data loss in the future.

-kol

7. Use stored procedures to make the system do heavy work

After solving many problems to produce a highly integrated database solution, my team decided to encapsulate some

The functional group of the associated table, providing a set of regular stored procedures to access each group in order to speed up and simplify the opening of client code

Send it. During this period, we found that the 3GL encoder set all possible error conditions, such as the following:

SELECT Cnt = COUNT (*)

FROM []

WHERE [] =

IF Cnt = 0

BEGIN

INSERT INTO []

([

< primary key column>

])

VALUES ()

END

ELSE

BEGIN

END

A non-3GL encoder does this:

INSERT INTO []

([

< primary key column>

])

VALUES

()

IF @ @ ERROR = 2627-- Literal error code for Primary Key Constraint

BEGIN

END

The second program is much simpler and, in fact, takes advantage of the functionality we gave to the database. Although I personally don't like to use embedded text.

Word (2627). But that makes it easy to replace it with a little pre-processing. A database is not just a place to store data.

Square, it is also a place to simplify coding.

-a-smith

8. Use find

The best way to control data integrity is to limit users' choices. Whenever possible, users should be provided with a clear value.

The list is available for selection. This will reduce errors and misunderstandings in typing code while providing data consistency. Some public data are particularly appropriate.

Co-search: country code, status code, etc.

-CS Data Architect

Part 5-Tips

1. Document, document

Document all shortcuts, naming conventions, restrictions, and functions.

-nickypendragon

Use database tools that annotate tables, columns, triggers, etc. Yes, it's a bit of a hassle, but in the long run, it's the opposite.

It is useful to send, support, and track changes.

-chardove

Depending on the database system you use, there may be some software that will give you some documents that you can get started with quickly. You might want to drive first.

Start by saying, and then get more and more details. Or you may want to schedule periodically, while entering new data along with your

The progress is detailed in each part. No matter which way you choose, always document your database, or in the database itself

Create documentation internally or separately. In this way, when you go back to the second version after more than a year, your chance to make a mistake

Will be greatly reduced.

-mrs_helm

two。 Use common English (or any other language) instead of coding

Why do we often use coding (for example, 9935A may be the supply code for ink pens, and 4XF788-Q may be the accounting code).

Code)? There are many reasons. But users usually think in English instead of coding. An accountant who has worked for five years may know

What is a 4XF788-Q, but not necessarily a newcomer. It is best to follow English when creating drop-down menus, lists, and reports

Sort by name. If you need to code, you can attach the English that the user knows next to the code.

-amasa

3. Save commonly used information

It is useful to have a table dedicated to general database information. I often store the current version of the database and the latest check / repair in this table.

Reply (to Access), the name of the associated design document, customer and other information. This enables a simple mechanism to track data.

Library, when customers complain that their databases do not meet the desired requirements and contact you, do so for non-client / server environments

It's very useful.

-Richard Foster

4. Test, test, repeat test

After establishing or revising the database, you must test the data fields with the data newly entered by the user. Most importantly, let the user test it.

Try and work with users to ensure that the data type you choose meets business requirements. Testing requires putting the new database into the actual service

It's done before.

-juneebug

5. Check the design

A common technique for checking database design during development is to check the database through the application prototypes it supports. let me put it another way,

For each prototype application that ultimately expresses the data, make sure you check the data model and see how to extract the data.

-jgootee

6. Access design skills

For complex Microsoft Access database applications, you can put all the main tables in one database file, but

Add other database files and load special functions related to the original database. Use these functions to connect to the main file as needed

The master table in the. Such as data entry, data QC, statistical analysis, providing reports to management or government departments, and various read-only

Inquiries, etc. This measure simplifies the allocation of user and group permissions and facilitates the grouping and partition of application functions.

It is easy to manage when the program must be modified.

-Dennis Walden

Thank you for your reading, the above is the content of "what are the methods of database design". After the study of this article, I believe you have a deeper understanding of the methods of database design. Specific use also needs to be verified by practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.