Some Background


Some Background.  While databases may seem like a strange realm to you, with experience you will start to develop quite a few insights.  One sometimes vague category of databases relates to product retail businesses.  I am going to use a company called Emilio Francisco as a sort of archetype.  This is not the correct name of the company I consulted for and with, but the basic ideas will hold true to pattern.

I first encountered Emilio Francisco when one of my MBA students who worked there did a project on it for the class I was teaching.  Emilio Francisco was a company in Connecticut that had distribution rights for Emilio Francisco Italian made shoes.  Ultimately, I was involved because one of my students, Frank D'Andrea was doing a project for them and he came and hired me as a subcontractor for technical issues.

Frank was in his last year as a CIS major at Quinnipiac University when I first got there in 1999.  He was one of several students that had been terribly frustrated that there weren't courses in web development, web application development and database driven web application development.  Even though he was in his last year he made sure to use three different projects he was working on as venues to develop the background he wanted.

Emilio Francisco is in New Haven County in Connecticut.  It deals primarily with shoes made by three companies.

  • Emilio Francisco
  • Gianni Versazzi
  • Dolce Durenza

They do not have their own stores.  They act purely as distributors to other retail outlets.

Product Databases.  Think about many of the things that retail businesses have in common, particularly if they are selling physical products.

  • customers
  • products
    • inventories
  • what each customer has purchased
  • vendors - product sources
  • billing

They may also have a lot of other things in common that get more elaborate than I want to get in this case such as

  • the firm might be involved in some of their own production and/or assembly
  • the firm might be involved in some of their own product development
  • multiple stores
  • special offerings that do not fit well with the physical product specification
    • maybe they offer special excursions
    • maybe they offer

I'm hoping that focusing on the most common issues contained in the first list will help you build some experiences and insights that will be highly portable to other similar sorts of databases.

Collect What?  So what sort of data is this shop looking to collect?

  • customer data
    • name and contact information
      • snail mail
      • e-mail
      • phone
    • products that each customer has bought
    • billing info for each customer
      • most transactions are completed at time of sale
      • some larger orders are given some sort of internal financing
  • products
    • inventories
    • vendors
      • vendor contact info

We will actually assume that all purchases at Emilio Francisco are completed when they are purchased.  This will simplify our database quite reasonably.  But it is important to try and conceptualize how allowing some sort of internal payments over time would impact the databases.