Single or multiple second table? - Still need help!

[garrowolf]

I am new to databases. I am trying to create a character creation program with this. I have a list of skills and a table under each skill for different ranks under each skill. Basically each skill has several ranks that reflect how good you are with that skill. Each rank opens up additional features. I created a table for the ranks and a table with all the different skills. Now I am working on the relationship between the tables. Do I link each skull in the first table to the same second table over and over or do I copy and paste one of each of the second table for each skill?

[robleyd]

I suspect from what you have said - and not said - that your table structure may not be ideal. I gather you have a separate table of ranks for each skill, probably with a lot of repetition across skills? If this is the case, your structure needs to be re-thought.

Can you give us some idea of your current table structure, how tables are related and especially the content of the skills and ranks? Perhaps upload a copy of your base file - [Forum] How to attach a document here Note maximum file size is 128K. If your file is larger, use a file sharing site such as Mediafire. The link also contains information on how to anonymise your document if it contains confidential information.

[garrowolf]

I will try to post the files later. Currently i just woke up for a moment.
I only have two tables at the moment. One table has a list of skills. The other has a list of skill ranks. I don't know if I should copy the skill rank table over and over so that I have one rank table per skill. Is there a better way to do that? Maybe make a new object type, like in OOP?

[Villeroy]

What is a "rank"? Just a number? A title? Which information describes a rank sufficiently? One rank may have a numeric value, a name, a description a creation date, a price (constantly moving price?), a date of expiry, ...
Same questions about the attributes of a "skill".

If ranks belong to skills, is it possible that a skill has no rank? Or how about a rank with no skill?
Is it possible that one rank belongs to many skills or vice versa one skill belongs to many ranks?
Could it be that skills and ranks are not related directly but through other entities? So one entity of the third kind has one rank and one skill? Many skills?

[garrowolf]

There are about 30 skills. Your rank in that skill says how good you are with it and what aptitudes you can learn.
The ranks are as follows: Untrained (-5), Basic (+0), Trained (+4), Proficient (+8), Focused (+12), Expert (+16), and Mastery (+20). Your skill roll is D20 + Attribute Bonus + Level Bonus + Rank.

Aptitudes are extensions to your skill. This could be like a specialization, a trick, or a variation. Examples of Drive aptitudes are Motorcycle, Big Rig, Off Road Terrain, and Sideswipe. There are three levels of Aptitudes. These levels are opened up by the rank you have in a skill. You have Trained, Focused, and Mastery aptitudes.

So I have a table with all the skills on it. It has a field for the skill total and a field that will have a list for what rank you have. Then I have a second table with the ranks listed. This will have the bonus as well as counters to regulate how many ranks you take in total. Also something to control which aptitude levels are visible to the user.

So this is only two tables at this point. What I am trying to figure out is how I set up the relationship with the tables. Do I need a separate rank table for each skill? Do I direct the relationship back to the same table over and over?

How is this done?

[Villeroy]

If there is only one person ("me") and every skill has one rank (positive/negative small integer), store the rank number in the skills table. If the rank descriptions "Untrained" ... "Mastery" are important, store them together with the small integer numbers as primary key in a small extra table and join the two integers in the relations designer.

In plain simple SQL:

Code: Select all

CREATE TABLE "RANKS"("RANK" SMALLINT PRIMARY KEY, "DESCRIPTION" VARCHAR(20));
INSERT INTO "RANKS" VALUES (-5, 'Untrained');
INSERT INTO "RANKS" VALUES (0, 'Basic');
INSERT INTO "RANKS" VALUES (4, 'Trained');
INSERT INTO "RANKS" VALUES (8, 'Proficient');
INSERT INTO "RANKS" VALUES (12, 'Focused');
INSERT INTO "RANKS" VALUES (16, 'Expert');
INSERT INTO "RANKS" VALUES (20, 'Mastery');

ALTER TABLE "SKILLS" ADD COLUMN "RANK" SMALLINT;
ALTER TABLE "SKILLS" ADD FOREIGN KEY ("RANK") REFERENCES "RANKS" ("RANK");

menu:View>Refresh Tables

Fill out the new ranks column in the skills table and then:

Code: Select all

ALTER TABLE "SKILLS" ALTER COLUMN "RANK" SET NOT NULL;

menu:View>Refresh Tables

[garrowolf]

I really appreciate your help with this. Unfortunately, I am just starting with databases so I have no idea what you just said. How would I do what you said in the program?

Thanks!

[Villeroy]

Well, then do your homework first before you start building your first database. This is not a tool for users. It is a tool for database developers. The outcome of development work is for users but not the development process itself.

[garrowolf]

Oh okay. Maybe someone else will help a new user. It was my first sentence.

[UnklDonald418]

It is common to those new to databases to do things backwards and start designing tables before the problem has been entirely defined and a strategy for a solution has been thought through. You said

I am trying to create a character creation program with this.

How does a character fit into the picture? That relationship may hold a solution to your conundrum.
Does a character have a skill or multiple skills?
Does a character have a rank or multiple ranks?
Will there be multiple characters in your database?

To get an idea of what is involved in designing a database look at this tutorial.
https://wiki.documentfoundation.org/ima ... torial.pdf

[Villeroy]

Modelling the items of a role game in a relational database is too big for an absolute beginner.

[MPEcho]

Modelling the items of a role game in a relational database is too big for an absolute beginner.

Probably. But IMHO, I learn more when I dream big and epic fail.
Example: you helped me get going with a split database on a stand alone server. I won't tell you how many times I crashed and burned, (only on a fraction of those did I come back to the list). but now it works. What a surprise! And I learned . . .

But cautioning about a very steep learning curve is a great idea. Particularly if someone is working on a hobby DB, not for business production. (If the latter, hire someone unless you have some chops already).

To the OP: I'd say go for it. Read the tutorial Unk Donald pointed out, do your best, try and ask intelligent questions here. But be prepared to scrap the whole thing and start over when you figure out you effed it up.

[Villeroy]

Pencil and paper is the first database design tool before you even decide which database product you are going to use.

[garrowolf]

I wrote my own system and it includes 26 different tech levels. Each tech level should have about 10 - 15 archetypes. These archetypes are examples for the players and NPCs for the GM. I have most of the archetypes for up to 20 tech levels at this point. What I want to do is have a database of all of these characters. Many of the characters, especially at lower tech levels will be the same. A blacksmith is a blacksmith. I want to make sure of all the math before I put it up for publication.

Each character has access to 20 - 30 skills. Each skill has multiple ranks. Certain ranks open access to more abilities.

I need to figure out if I can create one table with all the features of a skill covered and connect each skill to the same skill rank table or do I need to make a separate table for each skill.

[UnklDonald418]

As I suspected you are trying to define a direct relationship from Skills to Ranks, but it appears that they are actually indirectly related. The Character provides the missing link.

Each character has access to 20 - 30 skills. Each skill has multiple ranks.

Since each Character can have multiple Skills you have a one to many relationship between Characters and Skills which is best handled by an additional table.
Will each of a Character's Skills have a single Rank or can a Character have multiple Ranks associated with each Skill?
Can there be different characters with the same Skills but perhaps different ranks?
It appears that you are entering the realm of many to many relationships which require what are sometimes referred to as intersection tables. One might be enough but there is the possibility you might need more than one.

If each Skill assigned to a Character is associated with a single Rank you could have a table for Characters, another for Skills and still another for Ranks and finally an intersection table with an automatically generated Primary Key, an Integer field to store a Foreign Key value (the Primary Key value from the Characters table), and an Integer field to store to store a second Foreign Key value ( the Primary Key value from the Ranks table).

Data entry into the intersection table would be done on a Base form using List Boxes.
There you could select a Character and assign multiple Skills. For each Skill you could select one Rank.

Certain ranks open access to more abilities.

You might be able to accommodate that with an additional Integer field in the intersection table to reference a single added ability. If there could be more than one additional ability associated with a single Rank then you would probably need additional intersection table for this also.

[garrowolf]

Each character has access to the same list of skills. Each character would have several skills at various ranks. Each skill can only be of one rank. Each skill has an attribute (+-0 to 8), a level bonus (+1 to +8), and ranks.

Example:
Academics (T) +7
Beastcraft (F) +15
Computer (T) +7
Etc.

I'm trying to figure out if I should have one table for rank for all the skills like this:
download/file.php?mode=view&id=34138&si ... 04ac765043

One rank table

Or should I have a separate table for each rank like this:
download/file.php?mode=view&id=34139&si ... 04ac765043

[garrowolf]

Basically I am asking if you can use that second table as a function, like a programming language?

[UnklDonald418]

Looking at your last post it appears the Char_tbl violates the 1st Normal Form of database design which essentially says “a table must not try to keep multiple values for a piece of information, whether in one field or in multiple columns”. Tables designed with those problems often contain redundant data making them inefficient and it opens the door for a number of data corruption issues.

I uploaded a small demonstration that will hopefully get you headed in the right direction.
There are 4 tables
CHARACTERS has columns only for items with a one to one relationship. Name, Address, Birthday etc.
SKILLS has 2 columns ID a Primary Key, and SKILL a text name for a skill.
RANK has 3 columns ID a Primary Key, RANK a text name for each rank and VALUE a numeric weight for each rank.
CHAR_SKILL is an intersection table that ties everything together. It has columns ID a Primary Key, CHAR_ID an integer which stores a primary key value from the CHARACTERS table, SKILLS_ID an integer which stores a primary key value from the SKILLS table, RANK_ID an integer which stores a primary key value from the RANK table, and BONUS an integer that stores a value similar to the Bonus you mentioned in your post.

There is a form CHAR_ENTRY that displays existing information and allows for new data to be entered.
Select a name from the Characters table control and the associated skills will appear in the Skills table control just below. There is an associated Rank with each skill and a BONUS column for any bonus points they may have earned. Then there is a POINTS table that displays the sum of the BONUS and the VALUE associated with the Rank.
It is easy to edit an old or add a new skill using the Skills table control. A skill can be chosen from the List Box control in the SKILL column. Similarly a rank can be chosen using the List Box control in the Rank column. Type in a number in the Bonus column and hit the Enter key to save the record.
A Character can have as many skills as needed.
Try the Demo and then you can ask questions based on what you see there.

Demo_Characters_Skills.odb

(22.14 KiB) Downloaded 207 times

[garrowolf]

I'm trying to understand what you mean by, “a table must not try to keep multiple values for a piece of information, whether in one field or in multiple columns."

I have values that will be added together but they still will have different values.

For instance, Academics would have the total of the rank bonus + attribute + level bonus for that skill. Academics_Rank will just have which rank that skill has.

There are several versions of Attributes, which are either +0 if there is no value in them, or added together. Some of them need to have values of +0 to show that there is nothing added this time to this character from a specific source.

[UnklDonald418]

Yes, understanding how to apply the normalization rules can sometimes be difficult even for experienced designers. I thought the tutorial I referenced earlier did a decent job of describing the basics of the normalization process in Chapter 2. Database design is taught at the college level so there are a number of textbooks on the subject.
In your case if every character had only one skill then you could have a Skill column in the Character table.
If every character had every skill then it might be reasonable to include a Skill column for each skill in the character table. Since each character can have multiple skills but perhaps not every skill an additional table is needed.

There are several versions of Attributes, which are either +0 if there is no value in them, or added together. Some of them need to have values of +0 to show that there is nothing added this time to this character from a specific source.
For instance does every character with the Academics skill have the same attribute value for that skill? If the answer is yes then add an attribute value field to the Skills table.

If it can be one value from a range of values how is it chosen?
If the user doing data entry simply assigns an arbitrary value then an attribute value field could be in the intersection table.
If it is more complex you need to find a logical way to apply the value selection rules before you can know where to store those values (possibly another attributes table). It is often helpful to design a logic diagram. Remember computers only understand 2 logical states True (Yes) and False (No).

[garrowolf]

Okay, so I've been reading up on MySQL and I read the book that you sent me. I get what you were saying about the normalization. The question I have now is can I set up something like a function in MySQL like you would have in a programming language.

Example in pseudo code:
Upgrade_Skill (Skill, Current Level){
Decrease Total_Skill_Points by 3
Increase Skill Rank by one level
Increase number of aptitudes under skill by one

If Current Level > 8 then skill.attribute is +1
}

What my goal is to have a + button and a - button beside each skill and you can increase or decrease your stats till you like what you have. So I need that one button to do several things. Is that something that you can do in MySQL or do I need to use something like PHP to do the function?

[UnklDonald418]

SQL is an interpreted programming language that can execute complex statements. For instance if you go to Tools->SQL in the Demo_Characters_Skills.odb database I uploaded earlier and Execute the following statement

Code: Select all

UPDATE "CHAR_SKILL" SET "RANK_ID" = CASEWHEN((SELECT "RANK_ID" FROM "CHAR_SKILL" 
WHERE  "CHAR_ID" = 6 AND "SKILL_ID" = 0) + 1 < 7, 
(SELECT "RANK_ID" FROM "CHAR_SKILL" 
WHERE  "CHAR_ID" = 6 AND "SKILL_ID" = 0) + 1, 6),
 "BONUS" = CASEWHEN((SELECT "BONUS" FROM "CHAR_SKILL" 
WHERE "CHAR_ID" = 6 AND "SKILL_ID" = 0) - 2 > 0, (SELECT "BONUS" FROM "CHAR_SKILL" 
WHERE "CHAR_ID" = 6 AND "SKILL_ID" = 0) - 2 ,0)
WHERE "CHAR_SKILL"."CHAR_ID" = 6 AND "CHAR_SKILL"."SKILL_ID" = 0;

It should increase the Rank by 1 level, and reduce the Bonus by 2 for CHAR_ID = 6 (Wile E Coyote) and SKILL_ID = 0 (Academics)
The update does limit the Rank to no more than 6 (Mastery) and the Bonus to no less than 0.