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?
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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.