Database Design - when to split data into multiple tables?

I have a table of Animals:

Animals

Id Name

1 Dog

2 Cat

3 Rabbit

Each animal has a portfolio therefore I have two methods of defining the database tables.

METHOD 1:

Portfolio

Id AnimalId Date

1 1 01/01/2001

2 2 20/03/2009

3 3 05/03/2009

4 1 01/04/2005

METHOD 2:

DogPortfolio

Id Date

1 01/01/2001

2 01/04/2005

CatPortfolio

Id Date

1 20/03/2009

RabbitPortfolio

Id Date

1 05/03/2009

Which method is preferable? Bear in mind that this is a contrived开发者_Go百科 example and in reality I will be storing ~ 10k rows of data per portfolio.

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.