CODEWITHSUNDEEP
database-designdatabase-partitioning
guazz
guazz

Reputation: 1

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 example and in reality I will be storing ~ 10k rows of data per portfolio.

Upvotes: 0

Views: 1591

Answers (1)

David Steele
David Steele

Reputation: 3461

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.

Upvotes: 1

Related Questions