Beginning Database Design- P16: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 information in 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 relationships between those tables, not only to organize your data, but also to allow later retrieval of information from the database | Creating and Refining Tables During the Design Phase In Figure 10-8 the relationship between the CATEGORY_PRIMARY and CATEGORY_SECONDARY tables is one-to-zero one or many. What this means is that a CATEGORY_PRIMARY record can exist with no related CATEGORY_SECONDARY records. On the contrary a CATEGORY_SECONDARY record cannot exist unless it has a related parent CATEGORY_PRIMARY record. Similarly a seller does not have to have any history SELLER_HISTORY records but there will be no SELLER_HISTORY if there is no seller for that history to be entered against. Seller seller_id Category Primary primary_id primary seller popularity_rating join_date address return_policy international payment_methods Catego ry Secon da ry seconda ry_id Listi ng listing Seller_History primary_id FK secondary buyer_id FK seller_id FK secondary_id FK seller_id FK Category_Tertiary tertiary_id tertiary description image start_date listi ng_days currency starting_price reserve_price buy_now_price number_of_bids winning_price buyer comment_date listing comments Bid Buyer buyer_id buyer popularity_rating join_date address Buyer_History buyer_id FK seller comment_date listing comments buyer_id FK listing FK bidder bid_price bid_date Figure 10-8 Parent records can exist and child records are not absolutely required one-to-zero one or many . Child Records with Optional Parents A table containing child records with optional parent records is often typical of data warehouse fact tables such that not all dimensions need be defined for every fact. This is especially true where facts stem from differing sources such as BID in Figure 10-8. The result is some fact records with one or more NULL valued foreign keys. In Figure 10-8 a LISTING table record can be set as either being a secondary or a tertiary category. Thus the relationship between both CATEGORY_SECONDARY and CATEGORY_TERTIARY tables to that of LISTING is zero or one-to-zero one or zero. In other words a listing can be specified as a secondary
