Talking about the technique of database design (next)

Skills | design | data | database | Database design three, multi-user and the design of Rights management
It is not possible to develop database management class software without considering the problem of multiuser and user rights settings. Although the current market of large and medium-sized background database system software provides multi-user, as well as thin to a database within the permissions of a table to set the function, I personally suggest: a set of mature database management software, or should be the design of their own user management this piece of functionality, for two reasons:
1. Those large and medium-sized background database system software provided by the multi-user and its permissions settings are for the common properties of the database, and may not fully meet the needs of certain special cases;
2. Do not rely too much on the background database system software special features, a variety of large and medium-sized background database system software is not fully compatible. Otherwise, once you need to convert the database platform or the backend database system software version upgrade, the previous architecture design is probably not reusable.

Below see how to design a more flexible multi-user management module, that is, the database management software system administrator can add new users, modify the existing user's permissions, delete existing users. First of all, analyze user requirements, list all the functions that the database management software needs to implement, then classify these functions according to certain links, that is, the function that a certain type of user needs to use is classified as a class; Finally, start to build the table:
　　
function table (function_table)
Description of name type constraint condition
f_id int without duplicate function identification, PRIMARY key
F_name Char (20) is not allowed to be an empty feature name and is not allowed to duplicate
F_desc Char (50) allows for empty function description

User Group table (user_group)
Description of name type constraint condition
group_id int no duplicate user group ID, primary key
Group_name Char (20) is not allowed for empty user group names
Group_power Char (100) is not allowed for empty User Group permission table, the content is a collection of f_id of the function table

User table (user_table)
Description of name type constraint condition
user_id int no duplicate user ID, primary key
User_name Char (20) No duplicate user name
User_pwd Char (20) is not allowed for null user password
User_type int is not allowed for null-owning user group identity, and USER_GROUP.GROUP_ID Association

With this user group architecture design, when you need to add a new user, you simply specify the user group to which the new user belongs, and when the system needs to add new features or modify the old functional permissions, the functionality of the original user can be changed accordingly, using only the records of the Operation menu and the User Group table. Of course, this kind of architecture design has moved the function judgment of the database management software to the foreground, which makes the foreground development relatively complex. However, when the number of users is larger (more than 10 people), or the future software upgrade probability is greater, this price is worthwhile.

Four, the concise batch m:n design
Encounter m:n relationship, generally is to establish 3 tables, M one, n one, m:n one. However, M:n sometimes encounter the situation of batch processing, such as to library books, generally allow users to borrow at the same time, if the request to borrow records, that is, to list a user to borrow all the books, how to design it? Let's build the necessary 3 tables first:

Book table (book_table)
Description of name type constraint condition
book_id int no duplicate book ID, primary key
Book_no Char (20) No duplicate book number
Book_name Char (100) is not allowed for empty book names
......

Borrow user table (renter_table)
Description of name type constraint condition
renter_id int no duplicate user ID, primary key
Renter_name Char (20) is not allowed for null user name
......

Loan record form (Rent_log)
Description of name type constraint condition
rent_id int no Repeat lending record identification, primary key
r_id int is not allowed for empty user identities, and renter_table.renter_id Association
b_id int is not allowed for empty book identities, and BOOK_TABLE.BOOK_ID Association
Rent_date datetime is not allowed for empty borrowing time
......

In order to achieve a batch of query lending records, we can build a table to save the bulk loan information, such as:

Batch Lending table (batch_rent)
Description of name type constraint condition
batch_id int No repeat bulk borrow logo, primary key
Batch_no int is not allowed for empty batch borrowing number, same as the same batch of Batch_no
rent_id int does not allow for empty lending record identification, and RENT_LOG.RENT_ID Association
Batch_date datetime does not allow for empty bulk borrowing time

Is this a good design? Let's take a look at how do you want to find out all the books that a user borrows at a time? First, we search the Batch loan table (Batch_rent) to save the data of the rent_id field of all the records, and then use the data as the query condition to bring it into the loan record table (Rent_log) to inquire. So, is there any way to improve it? The following gives a concise batch design scheme, do not need to add a new table, just modify the loan record table (Rent_log) can be. The revised record table (Rent_log) is as follows:

Borrow record table (Rent_log)
Name type constraint condition description
rent_id       INT    &NB sp;    no repetition         borrowing record identification, primary key
r_id           int         is not allowed to be empty     user ID, Associated with the renter_table.renter_id
b_id          int          is not allowed for empty     book identification, and BOOK_TABLE.BOOK_ID Association
batch_no       int         is not allowed to empty     batch loan number, the same as the same batch of borrowed Batch_no
rent_date     datetime    is not allowed for empty     borrow time
...

Among them, the same loan batch_no and the batch of the first warehousing rent_id the same. For example: Suppose the current maximum rent_id is 64, then a user borrows 3 books at a time, then the number of 3 lending records that are inserted in batches is 65 batch_no. Then another user rented a set of discs, and the rent_id that inserted the rental record was 68. When using this kind of design, query the information of bulk borrow, only use a standard t_sql nested query. Of course, this design does not meet the 3NF, but compared to the above standard 3NF design, which is better? I don't need to tell you the answer.

V. The trade-offs of redundant data
A redundant field is retained in the "Tree relational Datasheet" of the previous article, where the example goes further-adding a redundant table. Take a look at the example: my original company in order to solve the staff's working meals, and a nearby small restaurant contact, daily food bookkeeping, the cost by the number of people, the end of the month by the company cash settlement, each month's work meal fee deducted from the salary. Of course, the number of people who eat every day is not fixed, and the cost of each meal is not the same because of the different dishes that are ordered at each meal. For example, Monday Chinese food 5 people spend 40 yuan, dinner 2 people spend 20, Tuesday Chinese food 6 people spend 36 yuan, dinner 3 people spend 18 yuan. In order to facilitate the calculation of each person's monthly work meals, I wrote a simple meal accounting management program, the database has 3 tables:

Employee table (clerk_table)
Description of name type constraint condition
clerk_id int no duplicate employee ID, primary key
Clerk_name Char (10) is not allowed for empty employee names

Per meal Total table (Eatdata1)
Name type constraint description
totle_id      INT     &NB sp;   no repeat         per table identification, primary key
persons        Char    not allowed for empty     Dining Employee identification Collection
eat_date      datetime    not allowed for empty     repast date
eat_type      char (1)       not allowed for empty     dining type, used to differentiate between dinner
totle_price   money        not allowed for null     per meal Total cost
persons_num   int          not allowed for empty     dining number

Meal Billing Table (EATDATA2)
Description of name type constraint condition
ID int no repeat meal billing fine table identification, primary key
t_id int is not allowed for empty per table identification, and eatdata1.totle_id associated
c_id int does not allow for empty employee identity identification, and CLERK_TABLE.CLERK_ID Association
Price is not allowed for empty per person for each meal

Among them, the table of meal billing (EATDATA2) is the record of each table (EATDATA1) of a record according to the dining staff split apart, is a fully redundant table. Of course, you can also combine some of the fields from each table (EATDATA1) into a meal billing table (EATDATA2). In this way, each table (EATDATA1) is a redundant table, but the design of the meal billing table more data duplication, compared to the above scheme is better. However, the table is a table (EATDATA2), the redundant tables, in doing the monthly meals per person statistics, greatly simplifies the complexity of programming, only a similar query can be used to calculate the monthly number of meals and meal fees General Ledger:

SELECT Clerk_name as Personname,count (c_id) as Eattimes,sum (price) as Ptprice to Eatdata2 JOIN Clerk_tabsle on (c_id=cle rk_id JOIN eatdata1 on (totleid=tid) WHERE eat_date>=convert (datetime, ' &the_date& ") and eat_date< DATEADD (Month,1,convert (datetime, ' &the_date& ')) GROUP by c_id

Imagine that if you don't use this redundant form, you'll have a lot of trouble counting the monthly meal totals per person, and the program will be more efficient. So when can you add a certain amount of redundant data? I think there are 2 principles:

1, the overall needs of users. When the user is more concerned about, the database of the specification records according to a certain algorithm for processing, then list the data. If the algorithm can be directly used in the background database system of the embedded functions to complete, at this time can be appropriate to add redundant fields, or even redundant tables to save these algorithms processed data. You know, for a large amount of data query, modify or delete, the background database system is much more efficient than the code we write ourselves.
2. Simplify the complexity of development. Modern software development, to achieve the same function, there are many ways. Although it is not necessary to require the programmer to master most of the development tools and platforms, it is still necessary to understand which method is more concise and more efficient than the one used for the development tool. The essence of redundant data is to use space to change time, especially the current hardware development is much higher than software, so the appropriate redundancy is acceptable. However, I still at the end to emphasize: do not rely too much on the platform and development tools to simplify the characteristics of development, this degree if not sure, later maintenance upgrades will be a big somersault.