Third, the design and development of multi-user and its rights management software, it is impossible to consider the problem of multi-user and user permission settings. Although the current market, the medium-sized background database system software provides multi-user, and the functions of the permission settings for a table in a database, I am personal suggestion: a ripe database management software, should still be self Designing the user manages this function, the reasons are two: 1. The multi-user and its permissions settings provided by the large and medium background database system software are all the share of the database, and it is not necessarily to fully meet the needs of certain special cases; 2. Do not excessively dependent on certain special features of the background database system software, multiple large, medium-sized background database system software is not fully compatible. Otherwise, once in the future, it is necessary to convert the database platform or the background database system software version upgrade, the previous architecture design is likely to not be reused.
Let's take a look at how to design a more flexible multi-user management module, that is, the system administrator of the database management software can add new users to the new user, modify the privileges of existing users, and delete existing users. First, analyze user needs, list all the functions required to implement the database management software; then, the function is classified according to certain contacts, that is, the function of a type of user needs to be used as a class; final start construction form: function Table (Function_Table) Name Type Constraint Condition Description F_ID INT No Duplicate Function Identity, Motor Keys F_Name Char (20) Does Not Allow Empty Function Name, not allowed to repeat f_desc char (50) allows empty function description
User Group (User_Group) Name Type Constraint Condition Description Group_ID INT No Duplicate User Group Identification, Motor Key Group_Name Char (20) Does Empty User Group Name Group_Power Char (100) Allows the empty user group rights list, content is functional table F_ID Collection
User_Table Name Type Constraint Condition Description User_ID INT No Duplicate User ID, Master Key User_name Char (20) None User Name User_PWD Char (20) Does not allow the empty user password User_Type Int Not allowed to be empty belongs to the user group identifier, and User_group.group_id association
The architecture design of this user group is designed. When you need to add new users, just specify the user group to which the new user belongs; when the system needs to add new features or modify the old function permission, only the operational function table and user The record of the group table, the function of the original user can change accordingly. Of course, this architecture design moves the function of the database management software to the front desk, making the front desk development relatively complex. However, this price is worthwhile when the number of users is large (more than 10 people), or the probability of software upgrade is large.
Fourth, a concise batch m: N design encounters the relationship between M: N, generally built 3 tables, M, N, M: N. However, M: N sometimes encounters batch processing, for example, to library borrowing books, generally allow users to borrow N this book at the same time, if requested by batch query, listed a certain approval of a user All books, how to design it? Let us build 3 tables: book (BOOK_TABLE) Name Type constraints Conditions Book_id Int No replica logo, primary key box_no char (20) No replica number book_name char (100) Do not allow empty book name ... ...
Borrowing User Table (RENTER_TABLE) Name Type Constrained Condition Description Renter_ID INT No Duplicate User ID, Master Renter_name Char (20) Does not allow empty user name ...
Borrowing Recording Table (Rent_log) Name Type Constrained Condition Description Rth_ID INT No Repeater Recording ID, Primary Keys R_ID INT does not allow empty user ID, and renter_table.renter_id association B_ID INT does not allow empty book identifiers, and Book_Table.book_id association Rent_Date DateTime Do not allow empty borrowing time ...
In order to implement a batch query borrow record, we can build a table to save the batch borrow information, for example:
Batch Borrowing Table (BATCH_RENT) Name Type Constrained Condition Description Batch_ID INT No Duplicate Birage Borrow Identification, Primary Key Batch_no Int does not allow empty batch borrow numbers, the same bicyclic batch_no the same RENT_ID INT does not allow empty borrow record identification, and rent_log.rent_ID Related Batch_Date DateTime does not allow empty batch borrowing time
Is this design? Let's take a look at all books for a certain user's book. How do you need to query? First, retrieve the bulk borrowing table (Batch_rent), save the data of all the Rent_ID fields of the eligible, and then use this data as the query condition to the borrow record table (Rent_Log) to query. So, is there any way to improve? Here is a simple batch design, you don't need to add a new table, just modify the borrowing record table (Rent_Log). The modified record table (Rent_log) is as follows:
Borrowing Recording Table (Rent_Log) Name Type Constrained Condition Description Rent_ID INT No Repeater Record, Primary Keys R_ID INT does not allow empty user ID, and renter_table.renter_id association B_ID INT does not allow empty book identity, and Book_Table.book_id association Batch_no int Does not allow for empty batch borrowing numbers, the same batch_no of the same branch is not allowed to be empty borrowing time ... where the same borrowed BATCH_NO and the RENT_ID of the first entry are the same. Example: Suppose the current maximum Rent_ID is 64, then a user borrows 3 books once, the BATCH_NO of the 3 borrowed records of batch insertion is 65. The other user rents a set of discs, and then inserts the rent_id of the rental record is 68. With this design, query information about batch borrows, just use a standard T_SQL nested query. Of course, this design does not conform to 3NF, but which is better than the 3NF design of the above standard? I don't have to say anything.
5. A redundant field is retained in the "Dataset Table" of the redundant data. The example here is further - add a redundant table. Let's take a look: I originally located in order to solve the work meal of the employee, contact with a nearby restaurant, eat accounting every day, the cost is flattened, the month is settled by the company, every month's work meals The salary is deducted. Of course, the number of people and people eating every day is not fixed, and because the cost of each donkey is different, the cost of each meal is different. For example, Monday Chinese 5 people spend 40 yuan, 2 people for dinner spend 20, 6 people spend 36 yuan on Tuesday, 3 people spend 18 yuan for dinner. In order to facilitate the calculation of each month's work meal, I wrote a simple dining account management program, 3 tables in the database:
Employee Table (Clerk_Table) Name Type Constrained Condition Description Clerk_ID INT No duplicate employee ID, primary key clerk_name char (10) does not allow empty employee name
Total Table (EATDATA1) Name Type Constrained Conditions TOTLE_ID INT No Repeat Each Metrodun Identity ) Do not allow empty dining types, used to distinguish, dinner TOTLE_PRICE MONEY does not allow empty total cost Persons_Num Int not allowed to eat empty meals
Extra dining fee detail table (EATDATA2) Name Type Constrained Condition Description ID INT No Duplicate Table Fine Table Identification, Primary Keys T_ID INT does not allow empty annual list identity, and EATDATA1.TOTLE_ID association C_ID INT does not allow empty employee identification Identification, and Clerk_Table.clerk_id Association Price Money not allowed to spend an empty person per meal, the record of the dining fee fine table (EatData2) is to open a record of the total table (EatData1) to open the dining staff. It is a unrelated redundant table. Of course, you can also merge the partial fields of each meal (EATDATA1) into the dining counting fee detail table (EATDATA2), so that the total table of each meal (EATDATA1) has become redundant tables, but the meals designed. The test table is more than the data compared to the above scheme. However, it is the redundancy table of the meal billing table (EATDATA2). When doing a monthly meal statistics, it greatly simplifies the complexity of programming, only use such a query statement to count each person Monthian dining fee general:
SELECT clerk_name AS personname, COUNT (c_id) as eattimes, SUM (price) AS ptprice FROM Eatdata2 JOIN Clerk_tabsle ON (c_id = clerk_id) JOIN eatdata1 ON (totleid = tid) WHERE eat_date> = CONVERT (datetime, ' "& the_date &"') And Eat_Date
Imagine, if you don't have this redundancy, every time you count the monthly meal fee receipt, you will have more troubles, and the program efficiency is also awkward. So, when can I add a certain redundant data? I think there are 2 principles:
1. The user's overall demand. When the user is more concerned, the data is processed by a certain algorithm for the specification record of the database. If the algorithm can directly utilize the built-in function of the background database system, it can be appropriately added redundant fields, even redundant tables to save these data processed after the algorithm. To know, for large queries of data, modify or delete, the background database system is much higher than the code we have written by. 2. Simplify the complexity of development. Modern software development, achieving the same function, there are many ways. Although it is not necessary to ask the programmer to master most of the development tools and platforms, it still needs to understand which method is more concise and more efficient. The nature of redundant data is to use space for time, especially the current development of hardware is much higher than the software, so appropriate redundancy is acceptable. But I still emphasize that don't have too much dependency platform and development tools to simplify development. If this degree is not grasped, the post-maintenance upgrade will be planted.
