[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.

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 on other sites
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 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 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 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 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 | LaserSkill76          | 750         | 500Where 76 is the PlayerID and 500 is 50% max skill and 750 is 75%.SkillsHierarchy table is:SkillParent | SkillNameRangedSkill | LaserSkillAmmunition table is:AmmoType | AmmoSkill1        | LaserSkillWhere 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.

 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 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 BaseSkillFROM  AmmoLEFT JOIN  Skills AS S1 ON    S1.SkillID = Ammo.RelatedSkillIDLEFT JOIN  Skills AS S2 ON    S2.SkillID = S1.SkillParentIDWHERE  AmmoID = 1;

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

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 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 on other sites
Quote:
 Original post by DrethonNow 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.

1. 1
2. 2
JoeJ
17
3. 3
4. 4
frob
11
5. 5

• 13
• 16
• 13
• 20
• 13
• Forum Statistics

• Total Topics
632185
• Total Posts
3004636

×