[Suggestion] Combine a couple quest related tables

Instead of having these:

creature_questrelation - Holds information about NPCs that start quests

creature_involvedrelation - Holds information about NPCs that end quests

gameobject_questrelation - Holds information about game objects that start quests

gameobject_involvedrelation - Holds information about game objects that end quests

why not combine the related sets into single tables. They only have two fields each and

pretty much have a similar theme:

creature_questrelation

id - the ID of the quest

npc_start - The creature_template.entry of the NPC that starts the quest

npc_end - The creature_template.entry of the NPC that ends the quest

gameobject_questrelation

id - the ID of the quest

go_start - The gameobject_template.entry of the GO that starts the quest

go_end - The gameobject_template.entry of the GO that ends the quest

Seems pretty simple, no need for two tables and makes bug tracking simpler since you

can just look up the quest in one place.

Looks brilliant for me.

Only problem is more than one NPC can start or end some quests, iirc…

Well, I looked in the tables, and, from a quick look, didn’t see any duplicated quest numbers…

If you are going to do it, get it down to one table and lose 6:

questRelation

type - 1 = NPC, 2 = GO, 3 = area trigger

id

start

end

– Brian

Yes, that’s fine though. You can have multiple entries in the existing tables already.

I had thought of that but wasn’t sure if you wanted to go that route or not. Seems like it makes the query overly complex. You’d have to rewrite every call to include the “type” field.

By just combining the two related tables, all you really have to do is edit the prepared statement to query creature_questrelation and gameobject_questrelation insead of the *_involvedrelation table. The calls in the code and even the prepared statement’s name can stay as-is.

/me thinks that MrSmite might want to look at the relations loading code /emoticons/default_smile.png

First, since they are loaded at startup there are no prepared statements. Second, there is already a relations helper that could very easily be modified to handle a single table…

– Brian

Well admittedly I hadn’t even looked at the code. I figured since we use prepared statements for everything else that these would follow suit.

Either way, combine related sets or all into one is better than the current setup.

SELECT COUNT(DISTINCT(quest)) FROM `creature_involvedrelation`

7809

SELECT COUNT(quest) FROM `creature_involvedrelation`

7814

Sorry.

Not sure what point you’re trying to make. That just shows that there are a few quests that have more than one NPC associated with finishing them which is actually wrong. Also, there are some NPCs missing too causing some quests to be stuck with a grey “?”.

Edit: Were you replying to Paradox? You didn’t quote anyone so…

And because there are quests with more than one quest giver and a single quest “turner” it makes your design inefficient.

5 quests makes it inefficient, really? Actually it doesn’t.

The majority of the quests are 1:1 (giver / turn in) and you’re duplicating the quest ID field for each one of those with the current design. With my design for those 5 anomaly quests you’d have the same amount of data as the two table method.

Example : Quest 40 - “A Fishy Peril”

Current design:

[ul][li]creature_questrelation.id = 241[/li]
[li]creature_questrelation.quest = 40[/li]
[li]creature_involvedrelation.id = 240[/li]
[li]creature_involvedrelation.quest = 40[/li]
[/ul]

My suggestion

[ul][li]creature_questrelation.id = 40[/li]
[li]creature_questrelation.npc_start = 241[/li]
[li]creature_questrelation.npc_end = 240[/li]
[/ul]

So if this quest had two starters you’d end up with 2 rows (6 fields total) in my table or 2 rows (4 fields) in your questrelation plus 1 row (2 fields) in involvedrelation, totaling 6 fields. But again, these multiple starter quests are the exception, not the rule.

Overall you’d save data and have a centeralized query by combining the tables.

Few more numbers…

SELECT COUNT(DISTINCT(quest)) FROM creature_involvedrelation – 7809

SELECT COUNT(quest) FROM creature_involvedrelation – 7814

SELECT COUNT(DISTINCT(quest)) FROM creature_questrelation – 7301

SELECT COUNT(quest) FROM creature_questrelation – 7491

195

SELECT COUNT(DISTINCT(quest)) FROM gameobject_involvedrelation – 448

SELECT COUNT(quest) FROM gameobject_involvedrelation – 450

SELECT COUNT(DISTINCT(quest)) FROM gameobject_questrelation – 516

SELECT COUNT(quest) FROM gameobject_questrelation – 520

6

When I said inefficient I wasn’t exactly referring to size (I’ll explain this later, I need to sleep)

Could you do a similar example but for quest 14111?

mrsmite, think about this, what is your primary key in this table? Maybe this will help you realize the issue.

What about quests that start with an item and ends on a creature/gameobject?

Also there are quests that start on a creature and end on a gameobject (and viceversa), try this.

SELECT COUNT(`quest`) FROM `creature_questrelation` WHERE `quest` IN (SELECT `quest` FROM `gameobject_involvedrelation`) -- 115 SELECT COUNT(`quest`) FROM `gameobject_questrelation` WHERE `quest` IN (SELECT `quest` FROM `creature_involvedrelation`) -- 206[/SQL]

Your idea looks good at first, but when you think all the posibilities there are many issues.

I fail to see how that’s pertinent. The primary key in the existing setup is both fields. The primary key in the new setup can be the same or even just the quest field. Either way it’s obvious that the primary key allows duplicates as Nay pointed out.

That quest has three possible starters and one finisher but again it’s the exception, not the rule. The majority of the quests are 1:1 so even if a handful of quests gives an extra row or two, overall you’re saving space.

/palmface

primary keys that allow duplicates? in what universe?

Also, I don’t think you actually read what Muzashi wrote.

Edit:

There is certainly a lot in the DB that could be streamlined or done better, but, I think it’s about time someone started doing sane Database planning (using flowcharts, or whatever) to map out what we currently have, and, only accept changes to the structure, if it improves on the existing plan, and has been properly thought out all the way through.

I started Encore’s DB design: http://ondras.zarovi.cz/sql/demo/?keyword=testtc

that is awesome