Database Desing - when to split data into multiple tables?

Answer 1

I would certainly go with method 1.

Because Portfolios are the same for each kind of animal then they should be in a table together. If Dog portfolios were different to rabbit portfolios then you might want to consider something more like method 2.

Method 1 can also be extended so you can add 1 row

4 Fish

and the rest of the structure carries on working. With method 2 you would have to create a table (e.g FishPortfolio) every time a row was added to your animals table.

Pretty much every time you come up with a solution that would involve creating Permanent Storage tables at runtime it is a terrible idea.

Make sure that you include both PortfolioID and AnimalID in your indexes.