CODEWITHSUNDEEP
mysqlsql
user3587554
user3587554

Reputation: 353

Advice on optimizing skills database tables

I'm looking for advice on how I can optimize my tables so I'm not performing a bunch of joins. Right now I'd have to perform 3 or 4 joins just for one skill, which is turned into a tree structure by my php. Could anyone give me tips on how to cut down the joins, and suggest any way to cut the number of tables I'm using? I don't know anything about game mechanics and I'm not a great db designer either. Any tips, and/or suggestions are much appreciated.

Stats:

use skilltree;

create table stats
(
    id int not null auto_increment,
    title varchar(28) not null,
    rate int default null, # default value for number
    txt varchar(38) default null, # allows stat to hold string value or number
    increase int, # amount to increase every level up unless string
    icon varchar(128), # path or url to icon, optional
    position int not null, # position used for display
    active int not null default 1, # is stat usable
    primary key(id)
)

Skills:

use skilltree;

create table skills
(
    id int not null auto_increment, # id used for prereq 0 is root
    specialty int not null,# id to the specialty it belongs to
    title varchar(20) not null, # name of the skill
    description text not null, # what the skill does
    tiers int(5) not null default 1, # levels of advancement in skill, default 1, max 5
    prereq int default null, # used as parent id, looks better than parent
    icon varchar(128), # path or url to icon, shown in skill description view
    image varchar(128) not null, # path or url to image, image of the stat
    active int not null default 1, # if this skill is actve and needs to be added to the tree
    primary key(id) # auto_increment key has to be primary key
);

Bonuses:

use skilltree;

create table skillbonuses
(
    id int not null auto_increment,
    skill int not null, # id of the skill bonus belongs to
    stat int not null, # id of stat bonus applies to for this tier
    tier int(5) not null, # level of stat bonus applies to
    dependant int default 0, # dependant stat
    bonus int not null, # bonus amount as a percent
    primary key(id),
    foreign key(skill) references skills(id),
    foreign key(stat) references stats(id)
);

Dependants:

use skilltree;

create table skilldependants
(
    id int not null auto_increment,
    bonus int not null, # bonus that requires the check
    numequals int, # value to check if number
    txtequals varchar(38), # value check if stat is a string type
    primary key(id),
    foreign key(bonus) references skillbonuses(id)
);

Upvotes: 0

Views: 112

Answers (1)

smcjones
smcjones

Reputation: 5610

Joins are not evil!

If your table is normalized, then you will find that many of your queries involve a JOIN, or multiple JOINs. They can frequently be the most effective way to keep your tables in good shape because you are not repeating data. When you repeat data you are at risk to have things get screwed up. The more data that is repeated, the more likely it will get screwed up. Complex joins offer no hit to the speed of your query, so the only potential downside is increased risk of carpal tunnel syndrome. On the contrary, the alternate possibility to smaller, nimbler tables is that you end up with a huge beast of a table that is repeating multiple values on each row, and that WILL be SLOW.

Can you normalize your way to fewer tables?

That being said, read the link I included and try to understand it a bit. Specifically, the point is that if you have any columns (other than primary keys), that show up in more than one table, you can normalize a bit more by dropping it from one of the tables. In that same vein, if you look at a couple of tables and realize you would NEVER call one without another, you can expect that merging them together is not going to cause you any serious problems.

However, it doesn't look, at a glance, like that is the case with you.

Other ways to cut coding

That being said, how can you limit the amount of these queries you write?

Utilize DRY principle. AKA Don't Repeat Yourself. If you find yourself hunting through your code to find a query to use at a later date, you are repeating yourself.

All queries should be in functions. That way when you want to call a query you are actually just calling a function. If your functions are well documented you will find that you have to write fewer and fewer queries.

Hope this helps.

Upvotes: 1

Related Questions