How should I model a “communication preferences” field which may require different supporting info for each row

database-design

I am building a system which sends information to other systems. Each client system has its own communication requirements. For example, a client system might require:

  • An HTTP request
  • An HTTPS request
  • An HTTPS request with some form of authentication
  • An email
  • A fax
  • A physical letter in the mail
  • A carrier pigeon
  • Other methods we have yet to discover

Each of these methods will likely require ancillary information. For example, a fax requires a fax number, while an HTTP request requires a URL.

Currently, we have a simple "client" table that looks something like this:

id           NUMBER
name         VARCHAR
description  VARCHAR

What is the best way to model each client's communication requirements in our database?

The simplest way would be to add a number of nullable columns to this table that store the information in the client's row:

id              NUMBER
name            VARCHAR
description     VARCHAR
comm_pref       VARCHAR  // would store things like 'email', 'fax', 'http', etc.
http_address    VARCHAR
https_address   VARCHAR
https_username  VARCHAR
https_password  VARCHAR
https_api_key   VARCHAR
email_address   VARCHAR
fax_number      VARCHAR
mailing_address VARCHAR
pigeon_name     VARCHAR

But not only does this look ugly, it doesn't scale well if new communication methods are added in the future.

Is there a good way to model this kind of data?

Best Answer

It depends on how much you care. For instance, there is a totally standardized method of doing this with URI. That will support HTTP/HTTPS/Email, and even with maps physical addresses. You can usually store them in the very database with very little work..

scheme:[//[user[:password]@]host[:port]][/path][?query][#fragment]

You can see from that, a lot is optional. They only require

  1. a scheme
  2. a colon (:)
  3. a host

There are lots of schemes specified. However some of your stuff is just goofy. It doesn't serialize requests, or address non-unique things like pigeons. What would that even look like? Globally addressable without a centralized authority (in this case a Global Pigeon Addressing Committee) is very difficult. You have to have a method of resolving conflicts (consensus).

Shy of that, you'll have to normalize the data and figure out what you really need to support. There is no database that is going to provide infinite abstractions without assumptions of use. You may as well just store your stuff in bytes.

Before you go down the EAV route, check to see if your database support binary JSON.

Related Question