0

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. I have tried to find ready made examples for this particular scenario, but all I have found are theoretical and simplistic examples which i cannot use.

I can made this diagram more simplistic but in that case I will have empty cells, so basically I'm in loop with this :)

Requirements:

  • Some companies are never contacted before. That's why I created two additional tables contact_emails, company_emails, contact_phones, company_phones.

  • Contacted company may have assigned contact (person).

  • Person can have phones and emails.

  • One person can be assigned to multiple companies

  • One company can have multiple persons

Diagram:

Diagram]

Questions:

  • Am I on the right track?
  • What can be improved and how?
Gilbert Le Blanc
  • 50,182
  • 6
  • 67
  • 111
pera
  • 1
  • 1
  • I'm not sure you need company_phones and company_emails, the design should stand without these. No database design should contain a circular reference of tables - there should always be a hierarchy. – Robbie Dee Jul 31 '15 at 08:23
  • I think I need them (probably im wrong). Emails can be assigned to company, (later when company is contacted) email can be assigned with person too and email can be assigned to both companies and persons. – pera Jul 31 '15 at 08:28
  • Yes, but you could do that from emails-->contact_emails-->contacts-->companies could you not? – Robbie Dee Jul 31 '15 at 08:30
  • No i cant, because contacts are not necessary associated with companies. For example i will not have contact person until i contact company itself. – pera Jul 31 '15 at 08:49
  • In which case company_id would simply be blank... – Robbie Dee Jul 31 '15 at 08:52
  • Alright, but arent empty fields a bad thing? From what i read if there are empty fields (and in my case it will be a lot of them, if i go with this route) that usually means that database is not well designed. – pera Jul 31 '15 at 08:57
  • Or you could simply have a company_contacts table between the two... – Robbie Dee Jul 31 '15 at 09:01
  • Arent then we just spinning around, replacing one table with another? IMO this database can be probably be much better designed... – pera Jul 31 '15 at 09:11
  • I think lose company_phones & company_emails and add company_contacts and it wouldn't be too far off... – Robbie Dee Jul 31 '15 at 09:41
  • Im spining my head for several days and i have no idea how new table company_contacts can help me. Any hint? – pera Aug 09 '15 at 21:42
  • Look at your last two requirements: **One person can be assigned to multiple companies** & **One company can have multiple persons** That is what company_contact would give you. It would just be a table whose primary key consisted of 2 foreign keys to contacts and companies. – Robbie Dee Aug 09 '15 at 21:59
  • Sorry for all this confusion. I meant how it can help me to lose company_phones and company_emails. One company can have multiple phones and emails. After company is contacted i may gather additional information (get person name, his phone number, email etc). I mean i need somehow to separate them... – pera Aug 09 '15 at 22:05
  • You should be able to do this via Companies-->Company_contacts-->Contacts and then out to Emails/Phones as required – Robbie Dee Aug 10 '15 at 13:03
  • But look, one company can have several phone numbers and emails which are at first **not associated** with contacts. So i will need somehow to separate `contact emails` and `company emails`. Hope im clear. If im left with just `emails` table, how can i know if particular email is from `contact` or from `company`? I mean i can add additional rows to email like contact_id and company_id, but it that case i will have a lot of empty fields in database. Contact email is not always associated with company. For example company can temporary hire someone to do marketing stuffs.. – pera Aug 10 '15 at 15:41
  • As I outlined earlier - if you have a circular reference of tables, that is usually an indication something is wrong. Perhaps it would help if you talked through how the tables are populated. Schemas are typically a tree structure with a strict hierarchy. – Robbie Dee Aug 10 '15 at 16:34
  • Ok, no circular references. How would you else solve this? Creating table `company_contacts` as you described will not solve my problem, because i need a way to separate company emails from personal emails. In the same time personal emails can belong to company. If you have answer, please post it. – pera Aug 10 '15 at 18:25
  • As I said earlier: **I think lose company_phones & company_emails and add company_contacts and it wouldn't be too far off** If you are trying to define rules for company emails and personal emails, this indicates the design isn't [1NF](https://en.wikipedia.org/wiki/First_normal_form) - that is, you are trying to store different types of values in the same table. – Robbie Dee Aug 10 '15 at 19:54
  • Man, i read all your answers here for days and i have no idea how else to solve this.. hence i asked for help. Is solution from http://stackoverflow.com/questions/12226260/store-multiple-email-addresses-in-database-for-different-user-types a good one? – pera Aug 10 '15 at 21:07
  • With regards to that link - you can do it that way, but you need to make sure you don't get clashes on the foreign keys, so some natural key would be need rather than int. – Robbie Dee Aug 11 '15 at 07:55
  • With respect, I don't think the email and phone linkage is the issue. You need to decide what the hierarchy is. Is this a CRM that contacts companies or contacts people? If it is really both, you might be better off hanging emails and phones off a contact table with person and company hanging off this too. Another alternative would be to denormalise people and companies into a contact table. – Robbie Dee Aug 11 '15 at 07:59
  • Also entities are typically named in the singular as it is self-evident that the company table contains companies. – Robbie Dee Aug 11 '15 at 08:00
  • As a final note - no prizes are given for perfect database design. The acid test is whether is supports what you need it to do. Once you have something on paper, run some scenarios through it and see if it stands. If it doesn't refine and repeat. – Robbie Dee Aug 11 '15 at 08:03

1 Answers1

0

Your not doing it wrong... much. ;)

Your requirement that one person can be assigned to multiple companies is not realized in the design. In order to include this, remove the company_id from contacts and add:

company_contacts (company_id PK/FK, contact_id PK/FK)

In contact_phones, I would rename the company_phones_id column to phone_id, to be consistent with the naming convention used in the rest of the design.

Is it correct that a company can have only one phone and e-mail, except via contacts? If not, check the cardinality indicators on the relationships between companies and company_emails / company_phones. If it's correct, you could (I'm not saying should) replace the company_emails and company_phones tables with email_id and phone_id columns in companies.

Besides these concerns, the schema looks acceptable.

There is one further adjustment I might consider. Adding a supertype for companies and contacts (we could call them parties) would allow you to combine the company_phones and contact_phones tables, as well as the company_emails and contact_emails tables:

parties (id PK)
contacts (party_id PK/FK, name, surname)
companies (party_id PK/FK, company_name, ...)
party_phones (party_id PK/FK, phone_id PK/FK)
party_emails (party_id PK/FK, email_id PK/FK)

However, in this case you couldn't enforce different cardinalities for company and contact phones/emails in the database.

reaanb
  • 9,806
  • 2
  • 23
  • 37
  • Thanks for answering! Company have multiple phones and email addresses, i did mistake in database design... Do you know perhaps how alternative design can look? What would you do in my case? – pera Aug 09 '15 at 21:37
  • Just change the primary key for `company_phones` to `phone_id` (assuming each phone can belong to only one company) and make sure you don't have a unique constraint on `company_id`. Similar for `company_emails`. – reaanb Aug 10 '15 at 05:20
  • Noted, thanks! Do you know of a better way to organize phones/emails? – RhymeGuy Aug 10 '15 at 10:05