Database Schema – Creating a Table with Many Optional Fields

normalizationschema

I am a developer (student) looking for some advice on what best approach should I follow while creating a table to model an entity that has a number of optional fields.

There exists a need to model an Organization entity with a few key fields such as id and name. There is also a join table from Users to Organization that specifies who belongs to the Organization and what their role(s) are.

The question I have pertains to a number of optional fields that belong to the Organization, such as website, email, and social links. Below are my ideas of approaching the problem thus far:

  1. Add them to the table as optional fields
    • Pros: Easy CRUD on one table, faster than navigating joins, etc.
    • Cons: It seems a bit dirty to me. Might it become difficult to do migrations in the future?
  2. Create a contact_information table that references an organization_id, references a contact_type_id (of website, email, Facebook, etc.) from a lookup table, and has a generic value field for the actual content.
    • Pros: feels cleaner, allows for an arbitrary number of contact types in the future
    • Cons: probably a lot slower. Tons more tables.

I am leaning towards #2, because it is a similar approach to what I'm doing for physical addresses, but I'm unsure whether or not it is the best solution as DBA is not my forte. If there is a 3rd or even 4th option that I am unaware of, I'd be really interested to hear of those as well.

Best Answer

Your second option is more flexible, but I'm not sure why you're worried about "tons more tables". Usually this would be done with a single table:

contact_types
-------------
  id (PK)
  name

contact_details
---------------
  id
  contact_type_id (FK to contact_types.id)
  value

organization_contacts
---------------------
  id (PK)
  contact_detail_id (FK to contact_details)
  organization_id (FK to organizations)

populated like this:

contact_types
-------------
ID  | name
----+-----------
1   | web_url
2   | facebook_url
3   | phone_1

contact_details
---------------
id  | type_id  | value
----+----------+-------
1   | 1        | www.stuff.com
2   | 3        | (111) 111-1111
3   | 2        | facebook.com/?profileid=stuff

organization_contacts
---------------------
id | contact_detail_id  | organization_id
---+--------------------+----------------
1  | 1                  | 1
2  | 2                  | 1
3  | 3                  | 1

This schema only has 3 tables (not "tons") and you can have as many contact types as you want. The joins aren't that complicated. Your contact_details table will be large as there is 1 record per piece of contact info, but unless you have "tons" of contact info for each organization and "tons" or organizations, this probably won't be too big a problem. ;)

You could also have a contact_details table which stores all of the fields. Something like this:

contact_details
---------------
  id (PK)
  main_email
  secondary_email
  web_url
  facebook_url
  linkedin_url
  myspace_url
  street_address_line_1
  street_address_line_2
  street_address_line_3
  city
  prov_state
  country
  postal_code
  phone_num_1
  phone_num_2
  fax_num

This structure is much simpler but is more static. If you don't plan to change your set of contact data very much, and you think that most records will have most (or above a certain threshold of) fields filled out, I suspect this would perform better.

Related Question