Instance table - ideas and looking for suggestions.
 

So I'm going to take a stab at instancing, and was thinking about storing the info for each instance in a table. I have some ideas for what to add, but I was wondering if anyone had suggestions on what else to add(or even exclude) prior to working on the code.

Here's what I have so far:

CREATE TABLE instance (
instid int(11) NOT NULL,
charid int(11) NOT NULL default 0,
zoneidnumber int(11) NOT NULL default 0,
short_name varchar(16) NOT NULL default 0,
port int(11) NOT NULL default 0,
advlevel int(4) NOT NULL default 0,
UNIQUE KEY charid (charid)
) TYPE=MyISAM;

instid - The ID of the instance.
charid - The ID of the character the instance belongs to.
zoneidnumber - The ID of the zone the instance takes place in.
short_name - The short name of the zone the instance takes place in.
port - The port number of the zoneserver.
advlevel - The adventure level, used to spawn NPCs on a certain level range.

charid will be unique, only one instance per character at any given time.

Shouldn't each instance allow for multiple characters? It won't be all that useful if groups can't get into the same instance. Or is it going to allow multiple ones in but belong to a certain character?

Each instance will be usable by multiple characters, it's just that each character can only have one instance ID associated with them. So a person can't have multiple instances they can enter at one time. If they choose another instance, the current instance they're assigned to will be deleted(for that person and anyone in their group).

I think the character associatiion needs a bit of work. I dont think that the charid belongs in this table.

possibilities I see:
1. make an instance table, with an instance ID (PK)... one per instanced zone.
Then make a char_instance table which associates players to instances.. if you make the charID the primary key in the association table, it enforces the 1 zone/player constraint.

2. take charid out of the instance table, add a field to the account table which is their instance ID. this also enforces your constraint.

I can help with the SQL to manipulate this if you want. just ask.

Hmm, I'd probably rather the instance ID be in the character_ table instead of the account table if it was removed from the instance table. What were your thoughts exactly on the various tables? I'm not sure I understand what you mean by setting up the instance table so that there's one instance per zone. Or did you mean one instance per character?

Edit: Nevermind, just read your post again and understand what you mean heh.

yea, my bad... character_ is what i meant, not account

Ok some new ideas, going by your suggestions.

1. add a field to zone that dictates whether or not the zone in question is an instance style zone. 1 = yes, 0 = no. Basically yes means that there can be multiple instances of the zone, no means that there can only be one of that zone for players to play in.

2. add a field to character_ called instid, this field stores the particular instance ID that character is assigned to.

3. in the instance table, remove charid and use this table as the general instance info...instid, zoneid, advlevel, etc. When the player zones it will check this table and if the target_zone id = the zoneid for their instid, then it will move them to that instance, otherwise it will continue like normal.

Couple things to think of:

shortname - Take it out. It is not needed there. zoneid is enough to identify the zone. If you want the name, get it from the zone table via a join.
port - What use is this? Especially without an IP. Zones do no tneed to be on the host.

Also, zoneid is a int32 where one half is the actually zoneid and the other (if non-zero) is the instance id. So, the way to do this would be to make sure that world is tracking zoneid -> zoneserver by full int32 and to boot zoneservers like normal. Then add some code to zone to query your table on zone bootup if the instance id portion of the zoneis is non-zero.

Also, to do this right you may want to look at the zone->world registration and have them register as "instance" zone servers. so that you can reserve certain zones for instance only. Like, perhaps:
Is still a normal dynamic zone, but maybeIs an instance zone. This could even be extended to be like to bring up certain instances per main zone, if you wanted to take it that far.

zone instancing questions
 

ok I realize there are some difficulties with instancing, but lets say you want a group to enter into this zone? now with a zone being instancted to just 1 character or accoutn, won't that mean other people in your group are excluded from entering into the instance?
Also the other thing that comes to mind is dynamic mob stats. Is it posibable to enter a varable as a field in an entery in the ncp_types table and take the value from a perl script for which an NPC take from the group average?

Mabey like someone said earlier, have a zone lets say guka renamed serveral times to things such as guka01, guka02 and so on in the eq client files. People don't need to download them just to make a copy of the guka files and rename it to the appropiate zone name. i do realize with each new instance created this way an entery will also have to be entered into the DB with the corrosponding name. But this is not really true instancing.

Just a though that came to mind upon reading this posting