-1

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?
Adam
  • 12,236
  • 9
  • 39
  • 44
  • I don't think this is appropriate to this forum. -1 – Leo Sep 14 '10 at 06:54
  • Why? Can you give a more specific reason? – Adam Sep 14 '10 at 14:18
  • 1
    From the FAQ: "Avoid asking questions that ... require extended discussion." This Q isn't a specific problem, you're asking someone to do your work or your line manager's work for you. – Leo Sep 14 '10 at 19:30
  • I agree that the question could have been more specific, but it seems rude to suggest that I'm asking people to do my work for me. I think the question was legitimate in that it was meant to solicit advice about best practices in database architecture. I guess I confused you by illustrating my question with real information from the project. – Adam Sep 14 '10 at 19:40
  • 1
    No, I think it's a bit of a cheek to post a spec, expect someone to read it and understand it, then to take some considerable time to assess and comment upon it. Most people here are working on their own projects being paid by someone else. Time is money. Everybody here is prepared to give some time and to help out others and, in return, often have their own problems solved, but with this one you're asking a lot. And I've already spent enough time on it. – Leo Sep 14 '10 at 19:52
  • 1
    I have to agree with @Leo. This is an extremely extensive question, most people would charge money for answering it in their professional life. Quite apparently you have put some time into this already, finding flaws in this design would take considerably more time and is probably pointless without also going over the detailed project requirements. I applaud your effort in formulating this question, but I doubt you'll receive any answers. You should trim it down to specific points **that you are concerned about**, that may be an answerable question. – deceze Sep 15 '10 at 04:04
  • 1
    @adam - Time consuming questions aren't always bad, but they have to be interesting: http://stackoverflow.com/questions/3684484/peak-detection-in-a-2d-array . Agreeing with @deceze, that this is usually something you pay a consultant for their expertise. – JMC Sep 16 '10 at 02:19

1 Answers1

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.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58