1、好友关系设计:
CREATE TABLE `character_social` ( `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Character Global Unique Identifier', `friend` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Friend Global Unique Identifier', `flags` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Friend Flags', `note` varchar(48) NOT NULL DEFAULT '' COMMENT 'Friend Note', PRIMARY KEY (`guid`,`friend`,`flags`), KEY `friend` (`friend`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Player System';
2、门派关系设计:
门派信息: CREATE TABLE `guild` ( `guildid` int(10) unsigned NOT NULL DEFAULT '0', `name` varchar(24) NOT NULL DEFAULT '', `leaderguid` int(10) unsigned NOT NULL DEFAULT '0', `EmblemStyle` tinyint(3) unsigned NOT NULL DEFAULT '0', `EmblemColor` tinyint(3) unsigned NOT NULL DEFAULT '0', `BorderStyle` tinyint(3) unsigned NOT NULL DEFAULT '0', `BorderColor` tinyint(3) unsigned NOT NULL DEFAULT '0', `BackgroundColor` tinyint(3) unsigned NOT NULL DEFAULT '0', `info` varchar(500) NOT NULL DEFAULT '', `motd` varchar(128) NOT NULL DEFAULT '', `createdate` int(10) unsigned NOT NULL DEFAULT '0', `BankMoney` bigint(20) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`guildid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Guild System';
门派成员: CREATE TABLE `guild_member` ( `guildid` int(10) unsigned NOT NULL COMMENT 'Guild Identificator', `guid` int(10) unsigned NOT NULL, ---玩家id `rank` tinyint(3) unsigned NOT NULL, `pnote` varchar(31) NOT NULL DEFAULT '', `offnote` varchar(31) NOT NULL DEFAULT '', UNIQUE KEY `guid_key` (`guid`), KEY `guildid_key` (`guildid`), KEY `guildid_rank_key` (`guildid`,`rank`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Guild System';
3、建立合适的sql,目的是为了增删改查,分析如下:
1)玩家的guid来自characters,扩展好友设计时,自然使用guid作为关联,欲描述玩家A和玩家B的关系,可能有多种情况,所以加个flag,那么如何增删改查呢?
增加好友关系: INSERT INTO character_social (guid, friend, flags) VALUES (?, ?, ?)
删除: DELETE FROM character_social WHERE guid = ? AND friend = ?
取玩家的好友列表: SELECT friend, flags, note FROM character_social JOIN characters ON characters.guid = character_social.friend WHERE character_social.guid = ? AND deleteinfos_name IS NULL LIMIT 255
更新好友关系: UPDATE character_social SET flags = ? WHERE guid = ? AND friend = ?
2)每个门派都有一个唯一id,每个门派里有若干成员,每个成员的guid唯一,所以下面这两句的用意于此吧:
UNIQUE KEY `guid_key` (`guid`),---玩家 KEY `guildid_key` (`guildid`),---门派id