0

I'm currently developing a small CRM for my own business use, however when come to database design I'm a bit stuck how to design it.

I need it to load fast without stressing the server or client side.

Here is the categories I need

  1. Customer:
    • Name
    • Address
    • Phone No.
  2. Visit:
    • Treatment Stage
    • Treatment Type
    • Treatment Date
    • Customer Name (Pull from Customer table?)
  3. Appointment:
    • Appointment Type
    • Appointment Date
    • Remarks
    • Customer Name (Pull from Customer table?)
  4. Transaction:
    • Transaction Type
    • Transaction Date
    • Transaction Remarks
    • Customer Name (Pull from Customer table?)

If let say I wanted to get those customers that doesn't visit me for more than a year, do I need to add a table in the visit? Or just use SQL query type to do the listing?

Your advise is highly appreciated.

Thanks, Jennifer

Jennifer Low
  • 1
  • 1
  • You can achieve that kind of report by SQL Query. [this post](http://stackoverflow.com/a/1415448/2627842) could be helpful for you. – Ariful Haque Jan 20 '15 at 14:43
  • @ArifulHaque means I can use the above plan and later just use your comment sql query to get the related data as I want, correct? – Jennifer Low Jan 20 '15 at 14:47
  • Yes, You can! I believe your above plan is just showing text here. You'll need to set `primary_key` for each tables _(e.g.) customer_id_ for Customer Table and in Visit table you've to make relation with Customer table using `customer_id` as `foreign_key` here. Visit table should have his `primary_key` as well, _e.g. visit_sn _ I hope it make you understand it. – Ariful Haque Jan 20 '15 at 14:52

1 Answers1

0

Not sure if you are asking whether your schema is good or how to get the right information so here's both:

I would update your schema to this:

Customer (p_key = CustomerId)

  • CustomerId (int)
  • FirstName (varchar)
  • LastName (varchar)

Phone (p_key = CustomerId, PhoneType, DateEffective) Customers may have multiple phone types (cell, home, work) and may give you multiples of those (got a new number). You can either overwrite or keep history with a DateTime the number became effective.

  • CustomerId (int)
  • PhoneType (varchar)
  • DateEffective (datetime)
  • AreaCode (varchar) - don't use numbers if possible to start with 0's
  • PhoneNumber (varchar) - don't use numbers if possible to start with 0's

Address (p_key = CustomerId, AddressType, DateEffective) Mailing and Physical Addresses, and history like Phone.

  • CustomerId (int)
  • AddressType (varchar)
  • DateEffective (datetime)
  • Street1 (varchar)
  • Street2 (varchar)
  • City (varchar)
  • State (varchar)
  • Zip (varchar) - don't use numbers since some zip's start with 0's

Treatment (p_key = TreamentId)

  • TreatmentId (int)
  • Type (varchar)

TreatmentStage (p_key = TreamentId, Stage)

  • TreatmentId (int)
  • Stage (int)
  • Description (varchar)

Visit (p_key = all columns) Customer could have multiple treatments/treatment stages on the same day.

  • CustomerId (int)
  • TreatmentId (int)
  • TreatmentStage (int)
  • DateVisit (datetime)

Appointment (p_key = CustomerId, AppointmentDate, AppointmentTime) You can combine AppointmentDate and AppointmentTime into AppointmentDateTime though this isn't how most people would think of it.

  • Type (varchar)
  • AppointmentDate (date)
  • AppointmentTime (time)
  • CommentId (int)
  • CustomerId (int)

Transaction (p_key = TransactionId)

  • TransactionId
  • Type (varchar)
  • TransactionTimestamp (datetime)
  • CommentId (int)
  • CustomerId (int)

Comment (p_key = CommentId) This gets your free form text out of all of your other tables and centralizes it in Comment. This will with performance as most of time you do not need to load the comments plus the data will likely be over the default page size.

  • CommentId (int)
  • Comment (varchar)

Code (p_key = CodeType, Code)

  • CodeType (varchar)
  • Code (varchar)
  • CodeDescription (varchar)

Code table should store your "Types" (ApportionmentType, TransactionType, Phone Type, etc.) so you have a list of the codes, their descriptions, and you can use that in join criteria if needed (reporting).

The query part is pretty straight forward:

SELECT c.*
FROM Visit v
INNER JOIN Customer c
on v.CustomerId = c.CustomerId
WHERE v.DateVisit > DATE_ADD(NOW(), INTERVAL 1 YEAR)
Michael Humelsine
  • 601
  • 4
  • 6