1

I'm probably doing this wrong, but here it goes.

I'm trying to build something like very basic CRM. I know there are plenty of them, but I want something to learn actually.

Requirements:

  • Some companies are never contacted before. That's why I created two additional tables contact_emails and company_emails.
  • The contacted company may have assigned contact (person).
  • A person can have phones and emails.
  • One person can be assigned to multiple companies
  • One company can have multiple persons

Diagram: enter image description here

Questions:

  • Am I on the right track?
  • What can be improved and how?
Kindle Q
  • 119
  • 7
RottenUser
  • 11
  • 1

2 Answers2

1

  1. Learn Table Inheritance (aka subtype/supertype)

    Individual   : Legal Party (":" meaning "inherits")
Organization : Legal Party

Email          : Address
Phone          : Address
MailingAddress : Address
WebURL         : Address

LegalParty -< PartyRelationships >- LegalParty
LegalParty -< ContactInfo >- Address

  2. Read up on existing data model patterns. Don't re-invent the wheel

Community
  • 1
Neil McGuigan
  • 8,653
  • 5
  • 42
  • 57
0

@RottenUser, you seem to be on the right-track, as far as I can confirm this, having looked at your db schema.

You asked: What can be improved and how?

Well, company_phones isn't really need, I think. As you can add a column in the phones table or contact_phones, sort of to filter out if the phone is private (i.e. personal mobile)/public etc.

Michael Johnson
  • 21
  • 2