Setting up a Table not sure where to start

[Wally Steadman]

Greetings all,

I play an Online Game and in the game a person can have more than one
character. I was thinking of starting a database to track the members of
the group I am in but wanted some advice or direction as to tracking
players.

Example would be that one of the characters is my MAIN character and the
other 4 or 5 would be my Alternate Characters. Should I have two Tables for
this? Like on Table for Main characters and then a linked table for
Alternate characters? Is it more efficient to have one table with all
Characters listed and then associate each character by an alternate field?
What I want to avoid is alot of changes, but sometimes, a persons main
character may change to be one of their alternate characters and one of
their alternate characters will become their main character.

Example:

Bob (Main)
Fred (Bob Alt)
Steve (Bob Alt)
Doug (Bob Alt)
Joe (Bob Alt)

Things change and now Fred is the Main and Bob, Steve, Doug and Joe now
become Alts to Fred.

Also there will be more information stored about a character for sure. Like
date Joined Group, Date of last advancement, Date awarded some medal or
honor etc....

So not sure on the design structure and would appreciate any advice or tips.

TIA Wally Steadman
Online game is Ultima Online by the way

 

[Rod Plastow]

Hi Wally,

The principles of relational database design indicate that you create
entities (implemented as tables) based on the commonality of their
attributes. ( I will now get streams of protest and probably abuse from other
posters.)

Let me translate that into English for you using your situation as an
example. I detect two entities from your explanation: character and player.
Character has a set of attributes and likewise player has a different set of
attributes. Bear in mind it is not necessary for every instance of the
entity to use all the attributes. One of the attributes associated with
character is type but when we come to 'normalise' the entities and attributes
we find we need to create a third entity that relates a character with a
player. This third entity contains the attribute for type that has
moved/migrated from character.

All the foregoing has been a long-winded way of suggesting you need three
tables: character, player and character type. It is this last table that is
most interesting. I suggest the table has the folllowing columns.

CharacterTypeId - a 'meaningless' primary key for the record - use Autonumber
CharacterId - foreign key of the character
PlayerId - foreign key of the player
Type - could limit values here to a lookup list: 'main'; 'alternate'; ...
Date - optional, you could track date created or whatever
....

Never heard of Ultima Online by the way.

Regards,

Rod

 

[David Cox]

It sounds to me like you need a table of players, a table of characters, and
a linking table containing one record for each player/character combination.
The link table has the player key. the character key, and a field
identifying that character as either "Main" or "alternate" for that player.

 

[Cheese_whiz]

I don't really see the need for the database given your limited description.
But I may be way off-base.

CW