Jump to content
  • Advertisement
Sign in to follow this  
Drethon

[web] SQL and indirection

This topic is 4353 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Is it possible to use indirection for choosing tables in SQL queries (MySQL specifically). I want to be able to determine what table I want to access based on values in another table. Though as I'm writing this it occurs to me that perhaps instead of creating a new table, I should merge the tables and add a column defining the specific type that the sub table would contain. I'm still curious about my question either way...

Share this post


Link to post
Share on other sites
Advertisement
Philosophical answer: Generally, if the data in 2 tables is interchangeable, that data should be in 1 table. So you shouldn't want to do this.

Practically though, I don't think what you want is possible in pure SQL. (Though I am not an expert.)

Share this post


Link to post
Share on other sites
I don't know of any clean way to do it in SQL. As the parent pointed out, generally if your data is that identical it should be in the same table (with a field and index partitioning it as needed). You could do it with a switch or if statement in SQL (I don't know if MySQL's subset of SQL includes that), but it will get ugly fast even in a stored procedure (not sure if MySQL supports those, either - I'm a PostgreSQL kinda guy).

Share this post


Link to post
Share on other sites
Basically what I'm doing is an inheritance type of approach where there is one table with all common data so all objects can be searched at once, then sub tables for more specific information. I think my problem was that when I had situations where I wasn't sure which sub table needed to be used, my architecture was bad.

Share this post


Link to post
Share on other sites
The best thing is to either use just one whole table, or two subtables.

For example you can have a table "items" and another table "dealers" with multiple dealers per item (each dealer having his own details), so you for each item you may want to have a multiple row detailed table which is why you possibly want separate tables instead of just one. If that is the case, take a look at the JOIN syntax in MySQL, that allows you to link a row from one table, to selecting a few rows from another table.

It is actually impractical to have one sub-table per item in your main table, except if each of these sub tables will have tottally different fields (columns) which just can't go into one table. In that case, you might have to combine this with your server side scripting to return the results from one query, process the results (using basic control structures) and choose which table to work with next. I think MySQL 5 has features to get this done without resorting to other scripting, but the performance time that you will save will be negligible, and there will be a slight learning curve involved for it, so it's practical to go back to your server side script and take your next step.

Share this post


Link to post
Share on other sites
Hmm, I thought I had this solved but it appears I was wrong.

What I have is three tables, Skills, SkillsHierarchy, Ammunition.


The sample skills table is:

CharacterID | RangedSkill | LaserSkill
76 | 750 | 500

Where 76 is the PlayerID and 500 is 50% max skill and 750 is 75%.

SkillsHierarchy table is:

SkillParent | SkillName
RangedSkill | LaserSkill

Ammunition table is:

AmmoType | AmmoSkill
1 | LaserSkill

Where 1 corresponds with Laser Beam.


What I'm trying for is when a player fires a weapon with AmmoType 1, it can be determined that the primary skill is LaserSkill and base skill is RangedSkill. For player 76 LaserSkill=750 and RangedSkill=500.

Is there a query that can get this or do I have to go a different route? Bear with me as I'm fairly new at MySQL though I'm getting pretty good as I can currently walk around the world, look at things, get things and drop things successfully.

[Edit] Oh, and I could easily do this with an if, else if block in my SQL functions but I'm trying to set this up so I don't have to update my function if I add new skills to the database.

Share this post


Link to post
Share on other sites
You could have a table with skill types and instead of storing the name of the skill under ammoskill you store an id pointing to the row of the corresponding skill. The player table doesn't actually have columns representing skill types but there is a table called player skills that just maps skills to a player.

Table: Characters
CharacterID
etc...

Table: Skills
SkillID
SkillParentID
SkillName
etc...

Table: CharacterSkills
CharacterID
SkillID
SkillRating

Table: Ammo
AmmoID
RelatedSkillID

Let me know if that makes any sense.

The query would be similar to....

SELECT
S1.SkillName as PrimarySkill,
S2.SkillName as BaseSkill
FROM
Ammo
LEFT JOIN
Skills AS S1 ON
S1.SkillID = Ammo.RelatedSkillID
LEFT JOIN
Skills AS S2 ON
S2.SkillID = S1.SkillParentID
WHERE
AmmoID = 1;


This allows you to add any number of skills without having to alter the database.

Share this post


Link to post
Share on other sites
<slaps forehead> I've been making this mistake since I started working in SQL. You're talking about a double relational table I think its called (or something similar). thanks you.

Share this post


Link to post
Share on other sites
Excellent, just as I was shooting for:

mysql> select PrimaryCharacterSkill.SkillRating as PrimarySkillRating
-> from CharacterSkills as PrimaryCharacterSkill
-> join Ammunition
-> where
-> Ammunition.AmmoType=1 AND
-> Ammunition.AmmoSkill=PrimaryCharacterSkill.SkillID ;
+--------------------+
| PrimarySkillRating |
+--------------------+
| 750 |
+--------------------+
1 row in set (0.00 sec)

mysql>
mysql> select BaseCharacterSkill.SkillRating as BaseSkillRating
-> from CharacterSkills as BaseCharacterSkill
-> join Skills as ParentSkill
-> join Skills
-> join Ammunition
-> where
-> Ammunition.AmmoType=1 AND
-> Ammunition.AmmoSkill=Skills.SkillID AND
-> Skills.SkillParentID=ParentSkill.SkillID AND
-> BaseCharacterSkill.SkillID=ParentSkill.SkillID ;
+-----------------+
| BaseSkillRating |
+-----------------+
| 500 |
+-----------------+
1 row in set (0.00 sec)


Now if I make the decision to implement unlimited depth to skill hierarchy I'll have to look into recursive selects or something but thats for later if I ever decide to try that...

Share this post


Link to post
Share on other sites
Quote:
Original post by Drethon
Now if I make the decision to implement unlimited depth to skill hierarchy I'll have to look into recursive selects or something but thats for later if I ever decide to try that...


If you get to that point it might be easier to store both the parent ID and the root ID for the skill. It adds an extra field but it removes the need to travel up the heiarchy to find the root skill.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!