Beginning Database Design- P17

Beginning Database Design- P17:This book focuses on the relational database model from a beginning perspective. The title is, therefore,Beginning Database Design. A database is a repository for data. In other words, you can store lots of informationin a database. A relational database is a special type of database using structures called tables.Tables are linked together using what are called relationships. You can build tables with relationshipsbetween those tables, not only to organize your data, but also to allow later retrieval of information fromthe database....
Nội dung trích xuất từ tài liệu:
Beginning Database Design- P17 Creating and Refining Tables During the Design Phase ❑ Western Union ❑ Cash ❑ Visa ❑ MasterCard ❑ American ExpressSo, the PAYMENT_METHODS field for a specific listing could be something like this: Cashier’s Check, Western Union, Visa, MasterCardThis string is a comma-delimited list. A comma-delimited list is by definition a multi-valued set. Amulti-valued set is thus a set, or a single item containing more than one possible value. 4NF demands thatcomma delimited strings should be split up. In the case of an online auction house, it is likely that thePAYMENT_METHODS field would only be used for online display. Then again, the list could be split inapplications. For example, the string value Visa determines that a specific type of credit card isacceptable, perhaps processing payment through an online credit card payment service for Visa creditcards. 4NF would change the OLTP database model in Figure 10-18 to that shown in Figure 10-20. Seller_Payment_Methods Seller seller_id (FK) seller_idCategory_Primary payment method seller primary_id popularity_rating primary join_date address return_policy international Seller_History ListingCategory_Secondary seller_history_id listing# secondary_id seller_id (FK) buyer_id (FK) primary_id (FK) seller_id (FK) tertiary_id (FK) secondary comment_date secondary_id (FK) buyer_id (FK) comments description image start_date Buyer Buyer_History Category_Tertiary listing_days buyer_id buyer_history_id currency tertiary_id buyer starting_price seller_id (FK) secondary_id (FK) reserve_price popularity_rating buyer_id (FK) tertiary buy_now_price join_date comment_date number_of_bids address comments winning_price Bid bidder_id (FK) listing# (FK) bid_price bid_dateFigure 10-20: Applying 4NF to the OLTP database model. 293Chapter 10 The sensibility of the application of 4NF, as shown in Figure 10-20, depends on applications. Once again, increasing the number of tables in a database model leads to more tables in query joins. The more tables there are in query joins, the more performance is adversely affected. Using the 4NF application shown in Figure 10-20, a seller could allow four payment methods as follows: Cashier’s Check, Western Union, Visa, MasterCard That seller would have four records as shown in Figure 10-21. SELLER_ID PAYMENT_METHOD 1 Cashier’s Check 1 Western Union 1 Visa 1 Mastercard Figure 10-21: Dividing a comma delimited list into separate records using 4NF. Reading SELLER records using the database model shown in Figure 10-20 would require a two-table join of the SELLER and SELLER_PAYMENT_METHODS tables. On the contrary, without the 4NF application, as for the database model shown in Figure 10-18, only a single table would be read. Querying a single table is better and easier than a two table join; however, two-table joins perform perfectly adequately between a few tables, with no significant effect on performance, unless one of the tables has a huge number of records. The only problem with the database model structure in Figure 10-20 is that the SELLER_PAYMENT_METHODS table potentially has very few records for each SELLER record. Is there any point in dividing up multi-valued strings ...