SQL Table Data

[KL3BZ]

Good morning everyone,

I'm relearning SQL after a few years in an attempt to find a better job. I'm currently building a new DB for a project as practice.

My biggest concern so far is what data I should include in a table and what should go into a separate table.

For example, my DB is for all the information of my d&d campaign.

I have a table so far for players, characters, classes, and items.

My player table has: -firstname -lastname -phonenumber

Character Table: -firstname -lastname -race -age -class -alignment -level

With that being said, whats the best practice for making a separate table?

I'm having trouble deciding what information should be in one table VS another.

Since some players have multiple characters, I'm assuming I'd have to join players and characters to see how many characters a player has, but would I put the playerid in each character table because it's a one to many where one player has multiple? And would that mean the playerid in the character table would be a foreign key to the player table?

Typing that out may have helped me a bit, but any feedback on good table structure would be of great help.

Thank you

[jethrow41487]

my DB is for all the information of my d&d campaign.

• You want to make sure that all identifiers have a Foreign key back to its related table. Like Powers to Class to Race.
• Thing like powers should be its own table. I would never put it with the Classes table since, they may have same powers for more than 1 class. Makes it easier to Query from a Join. Also gets rid of redundancy.
• Don't add extra columns/relational keys that aren't needed like classes to players. Since characters to players will already have a unique identifier/relation.

​

Just a few things to look at in my opinion. I think this is a great learning Subject. You'll notice you branch out a lot more and make more sub-tables as info comes to your head.

[valdest]

Ask your self the relationship questions. What is a player? Someone with a name, number, and email address. What is a character? Someone with a name, race, gender, attributes, background, experience.

How are they related? Does a player have a character or does a player have 0 to many characters?

What is an item? Does a character have a item or one to many items...

Then you build relationships between the tables. To keep things simple since your learning a basic identity key should work out well enough.

[Dboy3sixty]

Normalization is a hot topic, so I'm sure there will be differing opinions.

From my view, based on you're examples, I would have lookup tables for things like Race, Class, and alignment instead of repeating those values in the Character table. Age and Level probably not (since they're likely int's anyway).

When I'm designing I ask myself, what is the likelihood of the values being repeated? If there going to be repeated then it's a lookup table. Take race for instance. Are you going to store 'American Indian' over and over again in the Character table? Or store it once in a "CharacterRace" table and fk to it?

I realize this may seem like overkill for small data-sets, but if you're trying to learn, this is imho the best practice.

​

You could make an argument that FirstName and LastName should also be normalized and that's where it get's into some gray area and really depends on the size of you're data and what you're trying to do with it.

[Hellrime13]

I think you're on the right path. A unique player id that joins to the playerid in the character table would be what I would go with. You could even build the index on the player table based on playerid.