Tutorial on Database Schema - Slide 8 - Products at Amazon.com or Starbucks

Back   Next Slide

  1. Products at Amazon.com or Starbucks
    • Coffee
    • Books
    • Product Types (Inheritance and Recursion).
Amazon and Starbucks Customers
Products
Let's move on to the topic of Products. As you might imagine, this can be a complex area.
Two important aspects are Inheritance and Product Hierarchies.

Inheritance
  • This diagram shows a 'Products' table that holds details that are common to all Products.
  • It shows an 'Amazon_Books' table that holds details that are specific to Books from Amazon.
  • It also shows a 'Starbucks_Products' table that holds details that are specific to Products from Starbucks.
  • These two are Children of the Parent 'Products' Table.
  • Therefore, this concept is referred to as Inheritance and is very powerful in the real world.
  • On the diagram, it is shown by the circle with two short horizontal lines underneath it.
  • Data Modellers define these as 'Sub-Types' of a 'Super-Type'.
  • This diagram shows the Logical level of the Schema.
  • At the physical level, this can be implemented in one of three ways :-
    1. Three tables - exactly like the Logical level.
    2. Two tables - one for Amazon and one for Starbucks, with the common data items copied down from the Parent Table.
    3. One table - at the higher level, with common data items and those for Amazon and Starbucks all in one large table.
    Recursive Product Hierarchy
  • The line that comes out of the Ref_Product_Types Table and then goes back in again is informally called a 'Rabbits Ear',
    or more formally a 'Recursive Relationship'. It defines a Product Hierarchy.
  • It means that any Product can be part of a higher grouping of Products, such as Categories, Types and so on.
  • This is a very neat and powerful way to implement this concept.
  • In our example, the 'parent_product_type_code' is a reference to the next highest level in the hierarchy.

    Suppliers
  • I introduce Suppliers at this point because Starbucks and Amazon.com are both Suppliers of Products within our Schema.
  • This would be the starting-point for Schema involving a more detailed analysis of Suppliers.



    © DataBaseAnswers.org 2006