4

I'm trying to design a database for a simple text based game where the player characters have a large number of statistics that I want to track. Currently I have a few groups of related statistics, skills, finances, basic attributes, and a few others. There are quite a few fields that are 1 to 1 with a character which is leading me to the following design dilemma: Do I put all of these 1 to 1 fields in one very wide table or do I split them into tables that are themed on their group?

If I put all of the fields into one table, I am looking at a table with at least 20 columns, potentially more as more features come up. This would give me only one place to go to for information on the character and would make loading the data into the application very easy. However I imagine that updating records in such a large table would be a hassle since I would need to ensure that updating one field doesn't break the rest of the data.

If I split the data I would have 3 or 4 tables linked by the character id, each with no more than 10 columns. I feel like this would make the data easier to see from a data viewer point of view and updates would be less volatile, but when I have to gather all the player information I have to make multiple queries to the different tables.

I am curious what those with more experience in database design think about this. I have read up a bit on database normalization but that seems to focus on splitting out data that is joined by relationships instead of data that is 1 to 1. If it makes a difference I am using ASP.NET Entity Framework Code First to create the database in SQL Server Express.

Evan Frisch
  • 163
  • 1
  • 6
  • 5
    for what its worth, 20 columns doesn't seem excessive. – Erik Eidt Nov 14 '15 at 15:45
  • How about using a view? You could get the best of both worlds (or discover you didn't need to split the table in the first place) – Dan Pichelman Nov 14 '15 at 18:14
  • 1
    *"I have read up a bit on database normalization but that seems to focus on splitting out data that is joined by relationships instead of data that is 1 to 1"* - well, "1:1" **is** a valid relationship, and when you have that many columns, I bet the table is **not** proper normalized. – Doc Brown Nov 15 '15 at 20:21
  • @DocBrown but a _true_ 1:1 relationship is not possible in any SQL system that I know of since it would require inserting to two tables at the same time. – D Stanley Nov 16 '15 at 17:00
  • @DStanley: you are missing the point. I was referring to the argument of the OP where he assumes splitting a table into smaller ones by normalization rules would require a 1-to-many relationship, which is false. Normalization rules do also apply to 1-1 (or 1-{0..1}, if you prefer that) relationships. – Doc Brown Nov 16 '15 at 18:09
  • Thanks for the comments. I understand that 1:1 is a valid relationship and I understand that it could be normalized I just didn't see it as one that would be too helpful to split apart in my case based on that I could alone. – Evan Frisch Nov 17 '15 at 12:14

2 Answers2

1

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.

WillG
  • 176
  • 2
  • I hadn't thought about future changes to the application and how that should affect my database design, I will definitely keep those thoughts in mind as I build out the DB. Thanks for the information. – Evan Frisch Nov 17 '15 at 12:17
1

Currently I have a few groups of related statistics, skills, finances, basic attributes, and a few others.

Well, actually it depends. I highlighted the key word in your sentence.

If those groups are really "groups", why not to split them into tables? But the reason should not be "because the table went large" (at least when performance is not yet your primary concern), but because you see them as groups, that can be independently worked on.

Splitting data between tables slows access (because to aggregate it you will need several queries). But it gives you ability to independently work on this data, to view it, evaluate it and develop it independently.

Vladislav Rastrusny
  • 1,844
  • 12
  • 14
  • I didn't think about it that way before and typically only one group will be getting updated at a time. I'll definitely have to think more from this point of view, thank you. – Evan Frisch Nov 17 '15 at 12:15
  • @EvanFrisch You are welcome. [SRP](https://en.wikipedia.org/wiki/SOLID_(object-oriented_design)) can be applied to databases also. Well, most of the time :) – Vladislav Rastrusny Nov 17 '15 at 12:16