Why not let the text search engine like lucene or sphinx do all the work?
I use Lucene and have found it to be very good at searching multiple tables and fields for whatever you set as a target.
You don't say what your web front end is written in but both of these tools can be used with most languages.
You will be adding a lot of code to maintain in the triggers that watch for created/modified/deleted in all the tables. Wouldn't it be easier to add the option for admins to mark a column as "to be searched". Then write code to add this column to indexed fields?
I see that Sphinx has "offline index construction and incremental on-the-fly index updates" which would seem to allow you to add a field, rebuild the index offline and then bring the new index up.
It sounds like you are questioning if you need to split the master table up because there are so many columns. The short answer is no. I'll touch on a longer answer below.
In a simple way of looking at databases, it's about groups of datum and what those groups represent. Relational databases being relationship, or links, between groups of datum. So, when you think about what goes in a table, or group of tables, you are working out aspects of some "entity" by way of the data that represents this thing. As you already know, this is done with a master table and one or more child tables.
As an example, an employee. We have a name, dob, id number(s), address, phone number, hire date, and so on. Some of these things are 1 to 1 some are 1 to n. That's how we generally break out where things go from a design point of view. That gives us a master table and some number of child tables.
Most modern databases will not have a problem with a master table having what you think of as a large number of columns. Ten, twenty, a hundred, none if that matters to the database. So, from a logical design point of view there is no reason to break-up that master table. However, the real world and theory do not get along all the time. I must stress that these times are RARE, often a different way of looking at the data will allow you to express it in a different way and avoid the issue. If you find yourself having really huge numbers of columns, ask why they are there and try to look at them from another point of view.
Back to the employee example, phone number. There could be several numbers here. So do I make a column for home phone another for cell phone and yet another for work extension? I may, or I may make a child table for it. That table would have three fields, id, category, number. So, what looked like three columns in the master table is in fact moved to a child table. Same data, different way of looking at it.
I think this maybe close to home for your case, but I admit that I am guessing here. I could see a set of stats that represent a character looking like they are part of the master table but really, they could easily be express as a child table. You mentioned basic attributes and skills, both of which would raise a red flag for me if they are part of the master table. Again, I am making some assumptions based on my rpg experiences with both computer and pen&paper games. As your game evolves you may well find a reason to add a new skill, you may even add a new base attribute. You may change or throw out an existing skill or base attribute. Do you want your database to change too? Ideally, no your don't want to have to go back and change your database structure and then the code for the database layer and then system logic code layer and then the UI code layer. You want to changes like that to affect as little code as possible. So, maybe you have a skill table, in which each row is a skill. Likewise, a base attribute table with each attribute as a row. Note that I say row not column in these cases, just like with employee phone number.
I feel myself beginning to ramble, so now I stop.
So, that's a longer answer. To summarize it, no you don't need to break up a master table because it has many columns HOWEVER you do want to analyze those columns to make sure they can't be expressed another way, like child table rows.
Hope that helps.
Best Answer
Ultimately, it comes down to use and architecture.
Architecture
Does the system handle "any sport"? Is the idea that you put on your architecture astronaut hat, and build a generic system that can handle any future type of sport that may not even exist today?
If so, obviously having dynamically-named tables is a huge pain, so it would make sense to have a schema that supports n sports, if required.
That said, I have a very strong bias against this approach: this is almost always more work and leads to poorer results. Making a separate UI, schema, etc, for each sport will ultimately lead to better user experience and easier to maintain code, even if it does mean some superficial amount of duplication (how to avoid/minimize this is a separate question).
How do you handle players that play multiple sports? Do they get two entries (eg, you treat as different people) or are you trying to do something specific with them?
Use
So let's assume you don't do sports dynamically (eg, if someone wants to add a new sport, it requires development effort to add it).
Is there ever a time where you're displaying players (or any other object you mentioned) from more than one sport at a time?
I could see this for a search function, where you could search by player or team name (regardless of sport), but beyond that I can't imagine many use cases.
If you never need to do this, then your approach is perfectly fine. You can stop reading here.
Alternative Schemas
Views
I'm a fan of KISS. In 15+ years of software development, I continue to fall back to the philosophy "build the simplest thing that works".
So my initial reaction, assuming a cross-sport search function is really the only use case, is to create views:
Of course, if you add a new sport, you have to add to the view. It may also be useful to include other common information but really that's dependent on what needs to get shown.
I'd try to keep all the sport-specific stuff in the View definition, so the search code doesn't need to have much or any specific code (besides maybe knowing how to link to
/nhl/players/player-namevs/nfl/...or however your app does that).Table Inheritence
Table inheritance can work, but is pretty complex. I don't have a ton of experience with it, and in fact, I think every time I've been involved in evaluating it we ended up doing something simpler (like I'm suggesting here).
So personally, I have yet to find why this would be useful, but maybe there is a convincing use case (that I don't know of) that justifies the complexity (eg, Table inheritance solves the use case better than any other solution).
Separate tables for sport-specific attributes
You could do a single
playerstable which has attributes common to all players of all sports, and then another set of tables likenhl_players_detailsthat contains a playerId and columns with additional info on the player. If there are a ton of common attributes, or you have many uses of "all players from all sports" then this may make sense.Key value pairs for sport-specific attributes
Completely alternative approach: have a
playerstable (again, with common attributes like name) and then aplayer_datatable that hasPlayerId,Sport,Attribute,Value. The attribute names entered would be sport-specific. This allows you to essentially add new attributes without modifying the schema (your code would still have to know to load/display them of course). The drawback is you lose some integrity: value would typically be a string field, so your app code would have to be resilient and handle potential failures converting the stringvalueto a specific data type (like integer).This concept can of course apply to Teams, Games, etc.