I am building a internal crm which has contacts which are just people with first name, last name, email, phone etc. Contacts can be one or more types e.t.c. job candidates, hiring managers, customers, workers, sub-contractors etc... and based on the types there are more fields specific fields.

I am building the software in laravel using eloquent orm and my first thought is to have a contacts table/object which stores the common fields and then a has-one to the other types, each which contain the specific fields to those types. Otherwise I can just store all of the fields as nullables in the one contact table is is_candidate, is_worker etc.

Any recommendations on an approach that has worked well for your use case?

  • Sharing your research helps everyone. Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer. Also see How to Ask – gnat Oct 26 '18 at 13:04
  • If your ORM does handle it, you can use inheritance. Which mean that you'll likely have one table for all common fields, one for each derived class. The ORM can also handle the fact you want only separated tables however considering your requirments, the common tables for common stuff seems more appropriated. – Walfrat Oct 26 '18 at 14:58

I'm a fan of a model where your contact has a type field and an ID, where all the specifics are in other tables. I like it the most because you will, at some point, be asked "Hey, can you add a feature to change contact x into contact y?". With the mentioned model, all you do is change the value in the type field.

Another advantage is that a contact can have all the information attached to it via the ID. If a contact is an applicant and turns into an employee, you don't have to do anything but fill out the employee info. The applicant info doesn't have to be deleted either. You contact can thus become multiple types at once, you might even get rid of the type field and infer it from what data it does or doesn't have.

You can also make some very flexible user interfaces with this model.

Edit: if you think about it, any field like "is_worker" is always redundant. You know if it's a worker if the worker data is present. This should be a property in your application instead of a database field.

  • I've ended up going with this as the answer after doing further research and trials. In my particular application a contact can be multiple types at the same time and different types have different fields. By moving the different fields into other tables eg. contacts (1-1) candidate_profiles and contacts (1-1) worker profiles I can determine if the contact is a candidate and/or a worker if they have a profile of that type. – the-a-train Nov 3 '18 at 12:39

If you are starting from scratch I would advice a look into object/document (nosql) DB instead of a relational. This will allow you to have variability of fields/attributes on the entities.

However, for relational both are correct approaches. If what you described never changes then it would be more expedient to have null/empty fields on the table itself. While if this reality changes you can find yourself needing to sprout those fields in new related tables.

So my advice is plan carefully with all the knowledge that you have that you couldn't present here, but be ready to change.

  • While that could be true, considering that the whole application of OP isn't about only Contacts, this solution is probably not doable for OP. – Walfrat Oct 26 '18 at 14:59

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.