Master and multiple child database tables vs. master and a 'properties' table

In my Rails app, I want to allow the user to connect to multiple remote locations via different protocols (FTP, SFTP, S3, Dropbox). Each location has different properties:

FTP: host, port, username, password, is_passive
SFTP: host, port, username, password
S3: bucket, key (not s开发者_运维技巧ure these are right; I'm guessing)
Dropbox: username, password (again, I'm guessing)

What would be the more advantageous database table design for storing this information?

Option 1 (master table with tables for each protocol)

connection
    id
    name
    protocol

ftp_connection
    connection_id
    host
    username
    password
    port
    is_passive

sftp_connection
    connection_id
    host
    username
    password
    port

s3_connection
    connection_id
    bucket
    key

dropbox_connection
    connection_id
    username
    password

Option 2 (master table with a 'properties' table)

connection
    id
    name

connection_properties
    id
    connection_id
    property_name
    property_value

(don't worry, I'll be encrypting credentials)

---

Your case looks like an instance of the Gen-Spec design pattern. Gen-spec is familiar to object oriented programmers through the superclass-subclass hierarchy. Unfortunately, introductions to relational database design tend to skip over how to design tables for the Gen-Spec situation. Fortunately, it’s well understood. A web search on “Relational database generalization specialization” will yield several articles on the subject. Some of your hits will be previous questions here on SO.

The trick is in the way the PK for the subclass (specialized) tables gets assigned. It’s not generated by some sort of autonumber feature. Instead, it’s a copy of the PK in the superclass (generalized) table, and is therefore an FK reference to it.

Thus, if the case were vehicles, trucks and sedans, every truck or sedan would have an entry in the vehicles table, trucks would also have an entry in the trucks table, with a PK that’s a copy of the corresponding PK in the vehicles table. Similarly for sedans and the sedan table. It’s easy to figure out whether a vehicle is a truck or a sedan by just doing joins, and you usually want to join the data in that kind of query anyway.