Relational advice

[mcfmullen]

I am trying to build a relational database in which I have an RIBBONS table that list a bunch of ribbons and an ACHIEVEMENTS table that lists achievements.

 

The thing is, each achievement has four ribbons (yellow, white, red, blue). Each ribbon has stats associated with it (points, experience, etc).

 

My achievements table looks as such:

id - Primary Key

achPhoto - icon of achievement

achName - name of achievement

achDesc - description of achievement

yellowPoints - points needed for yellow ribbon

yellowXP - xp gained for getting yellow ribbon

whitePoints

whiteXP

redPoints

redXP

bluePoints

blueXP

 

My ribbons table looks as such:

id - primary key

ribPhoto - photo of ribbon

ribColor - color of ribbon

ribdesc - description of ribbon

 

My question is, how can I effectively link my achievements table to my ribbons table?

 

I was thinking of getting rid of the ribbons table and simply having each ribbon photo as a value in the achievement table, but I don't want to copy those values for every single achievement... I only want to store the photos once in my database.

 

I want to display on my webpage one table per achievement which shows the stats needed to attain every ribbon within that achievement.

 

I.e.

Achievement photo + name + description

Yellow ribbon photo + Points + XP

White ribbon photo + Points + XP

Red ribbon photo + Points + XP

Blue ribbon photo + Points + XP

 

Any help is appreciated since I can't get my mind around how to avoid duplicating the ribbon photos in my database.

[mcfmullen]

I'm still at a loss over how to avoid duplicating my ribbons in the database. Can no one help me figure this out?

[fenway]

I don't follow.

[roopurt18]

I'm assuming that you're attaching these to registered users or players.

 

In that case you'd have the joining table:

user_achievements_and_ribbons

user_id, achievement_id, ribbon_id

[mcfmullen]

it isn't that simple:

 

I have three tables:

 

user_achievements

-id

-achievement_name

-achievement_symbol

-acheivement_description

 

user_ribbons

-id

-ribbon_name

-ribbon_icon

 

ribbon_criteria

-id

-ribbon_name

-achievement_name

-ribbon_requirement

-ribbon_reward

 

Every achievement has every ribbon. Each ribbon has a certain criteria that needs to be met, and that criteria differs for each achievement.

 

Example:

 

Achievemnt 1: yellow ribbon needs 20 xp, gives 200$, white ribbon needs 50 xp, gives 400$, red needs 100 xp, gives 1000$, blue needs 200 xp gives 1500$

Achievement 2: yellow ribbon needs 10 friends, gives 100$, white ribbon needs 30 friends, gives 500$, red needs 50 friends gives 700$, blue needs 100 friends, gives 1000$

 

In that vein, I'm having a hard time relating the achievements (approx, 20) to each individual ribbon (4) to each ribbon criteria (achievements x ribbons).

 

My question is hence: Am I relating them properly, or is there a better way?

[roopurt18]

Your `ribbon_criteria` table is similar to the one I suggested.

 

I'm not sure why you've used `ribbon_name` and `achievement_name` as linking columns; the related tables have `id` columns so you should use that instead.

 

ribbon_criteria

id, ribbon_id, achievement_id, required_exp, reward

 

I've also dropped the extra `ribbon_` on some of the column names.  The table itself is called `ribbon_` so it's sort of implied the columns are related to ribbons.  Additionally, the requirement is experience, which is not stated in the column name `ribbon_requirement`.  And the reward is money, which is (again) not stated by `ribbon_reward`.  The way they are named now, the implication is the requirement is some sort of ribbon and the reward is some type of ribbon as well.

 

So IMO you're relating them fine as long as there is ever only one requirement and only ever one bonus / reward.  Should you later decide to add additional requirements and / or bonuses, you'll need to break `ribbon_criteria` into a many-to-many relationship design.