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

Question

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 sure 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) JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

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.