mysql - Relational Table Design For Single Object w/Multiple Types -

i creating database web application , looking suggestions model single entity might have multiple types, each type having differing attributes.

as example assume want create relational model "data source" object. there shared attributes of data sources, such numerical identifier, name, , type. each type have differing attributes based on type. sake of argument let's have 2 types, "sftp" , "s3".

for s3 type might have store bucket, awsaccesskeyid, yoursecretaccesskeyid, etc. sftp have store address, username, password, potentially key of sort.

my first inclination break out each type own table non-common fields being represented in new table foreign key in main "data source" table. don't have know table associated each type stored in main table , rewrite queries coming web app dynamically based on type.

is there simple solution or best practices i'm missing here?

what describing situation want implement table inheritance. there 3 methods doing this, described in martin fowler's excellent book, patterns of enterprise application architecture.

what describe first inclination called class table inheritance fowler. method tend use in database designs, doesn't fit well. method corresponds closely oo view of database, table representing abstract class , other tables representing concrete implementations of abstract class. data must queried , updated multiple tables.

it sounds want use called single table inheritance fowler. in method, you'd put columns of data in 1 table, discriminator column identify fields associated element type. queries simpler, although have deal discriminator column.

finally, third type called concrete table inheritance fowler. in mind, least useful. in method, give concepts of having kind of hierarchical data, , create single table each element type. still, there times when might work you.

all 3 methods have pros , cons. should consult links above see might work best in project.