using mysql tables, this is the direction i would go
members_table
user id PRIMARY NOT NULL AUTO INCRIMENT
login name
login password
character name
..other membership/community type information you may wish to add
--------------------
location_table
user ID
room ID
--------------------
rooms_table
room ID
room text ID (the general text for room ex: You are in a forrest)
room special/secret text ID (special text that occurs from discovering an item in that room ex "Your magic reveled a ..")
npc ID
shop ID
exit north ID
ext south ID
exit east ID
exit west ID
exit up ID
exit down ID
exit SE,NE,SW,NW ID etc.
--------------------
items_table
item ID
health bonus
mana bonus
exp bonus
gold bonus
etc.
---------------------
room_items_table
item ID
room ID
----------------------
text_table
text id
text
Basicly, id use the member table only for loging in, and community/chat related stuff.
Location table is relation table, you put the user id (from member table user id, and it tells you what room id they are currently in. If you change the table lay out, or find a different way one thing you WANT to do is something like this reference table. In a mud,the most common action is movement. So you want to be able to access the smallest sized table, with smallest amount of fields to change/alter room location. This table is a MUST.
rooms table is where all room information is stored, like what npcs are inhabited, what items, information texts etc. Lots of reference keys here. Youll notice that the general and secret texts are ids. They are foriegn keys into the text_table. NPC and Shop are also foriegn keys that reference to npc and shop tables I didnt explain. I think you can come up with those yourself using the items table scheme. NPC text should also be reference to the text table. Also the directional exit ID's are reference ids to the room table. When a character uses a move command, the location table should update his room id to the reference ID stored in the proper direction reference. Room ID 0 should not be a real room. You should use 0 or null for directions that arnt available.
Items table has all the item information. Every item has a unique id, and special bonuses. You might want to add a Name field, that references the text_table for a name.
Room items table is another reference table, that lists all the items and rooms they are found in. Multiple rooms may have same item. Example: (item sword id = 3)
3|23
3|38
3|283
4|23
23|23
3|40
where 4 and 23 are other item id's. The reference table isn't needed to be sorted, but perhaps to speed up a search routine to return all the rooms with an item, or all the items in room, you may want to keep the table sorted numericaly by item id.
text_table is another must. I lied about movement being the most common thing in MUDS, its really second. First place goes to text appearences. YOu want a table with all your game text. This goes from npc names, item names, room names-text, etc. (Player names are fetched from member table though).
Any table that has a field for text should be a reference field to this table.
So for example, should I be player 34, this is psuedo how i might go about displaying all that i need to display
Code:
room = mysql_fetch( location_table, 34 ); // find user id 34,and return room id
roomInfoStruct = parseCols( mysql_fetch_row( rooms_table , room) ); // get room general text ref id, exit ids etc)
roomText = mysql_fetch(text_table, roomInfoStruct.txtId);
etc.
print roomText;
print Exits;
I hope that helps you out. I wrote it spur of the moment, so I hope I didnt make it too confusing.
BTW, I might be interested in helping you with your mud should you welcome the help. Let me know.