2 tables or not?

[jimfog]

Suppose I am making a site where there is database of basketball and football players. Would you make 1 table for both basketball/football players or 2 tables for each? Explain your choice. Supposedly we made 1 table that holds the details of both basketball and football players. Is there a way I could distinguish the type of players in the table. That for example player X is a basketball player and Y a football player. Of course such a distinction would not be necessary if we made 2 tables-it would be needed only in the case where we have 1 table.

[Ingolme]

You could have a field of type ENUM with value "basketball" and "football". It depends on if you're planning to always display them separately or not. If you never have basketball and football players on the same page then you should make two tables.

[niche]

That's a perpetual question. I currently believe a few smaller tables will lead to fewer bottlenecks than one large one.

Edited June 12, 2012 by niche

[jimfog]

That's a perpetual question. I currently believe a few smaller tables will lead to fewer bottlenecks than one large one.

You could have a field of type ENUM with value "basketball" and "football". It depends on if you're planning to always display them separately or not. If you never have basketball and football players on the same page then you should make two tables.

You are saying that if I do not intend having both basketball players and football players in the same page, then I should opt for 2 table. Yes in order to distinguish them though I might as well have a query using a WHERE clause-and so sticking with the one table scheme. As I see it the only reason I should use 2 tables is the reason that niche mentions-smaller tables lead to fewer bottlenecks. Do you agree with that?

That's a perpetual question. I currently believe a few smaller tables will lead to fewer bottlenecks than one large one.

That seems sound reasoning to me.

[birbal]

footballer and basket baller both are player. you can think it them as subclass of player. if footballer and bascekball player objects share same attributes for now so you can put them in one table as considering player of different types as ingolme sugested, distungiusihing by an enum field. if footballer and bascket ball player has more attributes rather than common attributes of 'player'. i would split two more different table for footebller and bascketballer where both table will hold specific information about footballers and bascket ballers. and both will link up to the the 'player' table. 'player' table will hold the common attributes. It will help you to make scalable database which can grow later. so that near future if you want to add more type of players or add more attributes to existing one. you dont have to change much of you existing database structure. so it depends on your application. it may be overkill for small project but will be effective where project is large or has oppurtunity to grow larger.