1

Some background I have a set of data that represents the alchemy ingredients and their effects from Skyrim. If you're unfamiliar with this you can combine 2-4 ingredients to make a potion. Each ingredient has 4 effects. If any effects between ingredients are the same it will make that type of potion. I've identified this as a many-to-many relationship and I set up my tables like so:

ingredients: ing_id (key), ing_name, (other supplemental info)

effects: eff_id (key), eff_name

ing_eff_xref: eff_id, ing_id

I would like to input 2 or more available ingredients and return possible combinations without knowing what the effects are. My sql experience is pretty much limited to phpmyadmin and simple select queries. I guess my questions are: is this the right way to structure the tables for this type of relationship, do I need to set foreign keys if I don't plan on updating the tables, and is there a query that can take a set of ing_names and return only eff_names that intersect?

Here is the mysqldump of the db if anyone is interested: http://dl.dropbox.com/u/59699040/alchemy_db.sql

pdizz
  • 4,100
  • 4
  • 28
  • 42

1 Answers1

1

is this the right way to structure the tables for this type of relationship?

Yes, but then you don't need to have effect1 through effect4 on the ingredient table.

do I need to set foreign keys if I don't plan on updating the tables?

Yes. The only way for you to get the data that you're after is by JOINing three tables together. Without foreign keys (or more specifically, appropriate indexes), that may not perform well on queries. Of course you do have a small number of rows overall, but using foreign keys is a good practice to follow in this type of scenario.

is there a query that can take a set of ing_names and return only eff_names that intersect?

I think you're after something like this:

SELECT e.eff_name
FROM ingredients i
INNER JOIN ing_eff_xref ie ON ie.ing_id = i.ing_id
INNER JOIN effects e ON e.eff_id = ie.eff_id
WHERE i.ing_name = 'Abecean Longfin ';

If you need to see effects for multiple ingredients, you could adjust your WHERE clause, like this:

WHERE i.ing_name IN ('Abecean Longfin ','Eye of Sabre Cat ','Bear Claws ');

You'll probably not want duplicate effects, so you could do a SELECT DISTINCT to eliminate those.

Can potion effects stack in Skyrim? If they in can stack, then you can do a GROUP BY query with a COUNT to get the stacked value of each effect:

SELECT e.eff_name, count(*) as value
FROM ingredients i
INNER JOIN ing_eff_xref ie ON ie.ing_id = i.ing_id
INNER JOIN effects e ON e.eff_id = ie.eff_id
WHERE i.ing_name IN ('Eye of Sabre Cat ','Bear Claws ')
GROUP BY e.eff_name;

This query will list 6 effects with a value of 1, and "Restore Stamina" will have a value of 2. Not sure if Skyrim potions work this way or not, but it was just an extra thought.

Aaron
  • 55,518
  • 11
  • 116
  • 132
  • Thanks for getting back to me. The second query is definitely a workable solution. The effects don't stack but there can be more than one effect that match so I can just pull out any effects that have a count greater than 1 in php. I'm not sure what's going on in the first query but I think what I would need is the opposite of SELECT DISTINCT if such a thing exists. Thanks for the help, it's been an eye-opener. – pdizz Jan 31 '12 at 14:41
  • Now that I know what to look for I found this article on finding duplicates http://www.petefreitag.com/item/169.cfm it looks like i could add HAVING ( COUNT(email) > 1 ) but using eff_name to narrow it down. – pdizz Jan 31 '12 at 15:02