Printable View
Let's say I have Type1-Type5 Tables. Each Type has one or more info linked to it. The info table for all 5 types have the same schema. Should I just make one info table and connect the 5 Type tables to it? Or should I make 5 separate info tables, one for each Type.
Would search speed be faster if I make 5 separate tables?
What is the data these tables would hold and how would they be linked?
Your question about faster depends on the number of rows expected and the backend database.
If you are using MS SQL SERVER for instance then if you decide to make one table for all 5 types then putting the "TYPE" column as the first column and "clustering" the primary key will in effect make it work like 5 separate tables in regard to speed of access to any of the types.
But if you expect no more than a couple of thousand rows then speed won't be an issue anyway.
My nature is to make a single table and have a type column - keeping things simple would lead me to that. I dislike designs that have many, many tables - it's confusing and hard to use on the output and query side.
The fact that all 5 table types have the same schema is a plus in this argument. But I would even create a single table if the 5 schemas didn't match 100% - basically the amount of mismatch has to be large enough before I would consider 5 different tables.