Guidance on Database Design for CakePHP

Question

I'm developing an application using the CakePHP framework and am currently in the process of designing the database. I want to make sure that I design the objects and their associations correctly so that the application performs well, is organized properly, is extensible, and scales well. I will first describe the application in detail, as well as propose an Object Relationship model. I need some guidance in terms of best practice and CakePHP convention. Please read through the description, then address my questions below.

Please leave a comment if you need anything clarified.

Application Background and Requirements

The application will function as a specialized CRM of sorts. The client will be using it to track and manage customers, contacts, jobs, inventory, vendors, and opportunities. The client currently has a large database that is unorganized and application code that is broken. They need to have the database rebuilt from scratch using the CakePHP ORM and a new application built on top of the data structure. The database will be built in MySQL. The application will need to support multiple user types with access control lists. It needs to be designed to scale efficiently, and new applications will need to be built on top of the object model at a later date.

Proposed Objects (sub-bullets inherit from object above)

• Company - any company the client does business with
• Customer - a customer of the client
• Contractor - a firm contracted by the client to do work
• Vendor - a company that supplies parts or services to the client
• Manufacturer - a company that manufactures parts
• Account - An object that encapsulates the relationship between the client and its customers
• Job - A unit of work provided by the company
• Task - A billable service associated with a specific job
• Part - An item installed by the client as part of a job or task
• InventoryItem - A part that is in inventory
• Person - A record of a human being
• Employee - An employee of one of the companies the client deals with
• Technician - A service technician employed by the client
• Contact - A contact person for a particular company the client deals with
• Administrator - A website administrator
• Opportunity - A potential job opportunity for any given account

Proposed Relations

(HO = hasOne, HM = hasMany, BT = belongsTo, HABTM = hasAndBelongsToMany)

• Company
  • HM: Contact, Technician, Employee
  • HABTM: Administrator
• Manufacturer Inherits from Company
• Vendor Inherits from Company
• Contractor Inherits from Company
• Customer Inherits from Company
  • HM: Account
• Job
  • BT: Account
  • HM: Task
  • HABTM: Part, Contractor, Technician
• Task
  • BT: Job
  • HABTM: Part, Contractor, Technician
• Part
  • HABTM: Vendor
• InventortyItem
  • HO: Part
• Employee Inherits from Person
  • BT: Company
• Technician Inherits from Person
  • BT: Company
• Contact Inherits from Person
  • BT: Company
• Administrator Inherits from Person
• Opportunity
  • BT: Account

I've done my best to illustrate this on a UML diagram, although I'm not sure if I've gotten the notation conventions correct: http://twitpic.com/2o5r0a

My Questions and Concerns

1. What changes, if any, would you make to this schema and why?
2. What is the best way to handle object inheritance in CakePHP?
3. What is the best way to walk the client through this schema, explaining how it meets the project requirements?
4. Does this proposed design pose any potential scalability issues?
5. Do you forsee this design causing any difficulties when developing the application code?
6. Any words of wisdom from CakePHP vets on this?

Answer 1

I want to help you out, but I don't want to do your work for you.

There are several CRM databases diagrammend over at Database Answers.

Pick a few, and compare them to your design. You'll get a handle on what you might be doing right and what you might be doing wrong.