Web site directory - There s also a one-to-many relationship between items and
There s also a one-to-many relationship between items and users. The Transactions table, once again, provides the map describing which users purchased an item. Pick any one item, say 10003, from the Items table. Find records in the Transactions table that have 10003 in their ItemId field, and you have a link back to each user that purchased that product, as shown in Figure 11-7. In real life, of course, you don t draw lines between records in tables to get information. In fact, you don t look at the tables at all instead, you create queries to get information. However, while I m on the subject of lines that connect things between tables, any time you extract data from all three of the tables (users, transactions, and items), your query must contain fields of all three tables. And the primary and foreign keys that link the tables must be connected by join lines in that query, as shown in Figure 11-8. We ll discuss the roles of the primary and foreign keys in detail as we progress through the chapter. So that s how database design works, in a conceptual sense. When there is a natural many-to-many relationship between items in two separate tables, you need a third table that contains records stating who purchased what. That third table provides the many-to-many link needed to extract meaningful data from the tables. Figure 11-8: A query s view of connecting lines between tables. Users Transactions Items Figure 11-7: Trace any one ItemId back to users who bought that item. 226 Part III: Personalization and Databases
We highly recommend you visit web and email hosting services if you need stable and cheap web hosting platform for your web applications.