Hi Everyone,

I'm just at the start of database project to see if I can reproduce some kind of Management Game.

I'm quite focussed on making sure I have an efficient design for the database at this stage.

I've created most of my tables, and have also created most of the relationships between them, however I've got a couple of issues which I'm ummming and ahhhing about, to which I thought I'd see if anyone in here could help me.



1. I have a player table which will contain numerical values from 1-20 (Not integers) for different player attributes. The value of those attributes will be looked up and a text entry returned for any reports which would be seen by a user.

I want to do this for more than one type of attribute (i.e. Strength, Speed), is it more efficient to store all the text entries for those attributes together in one Attributes Table, or to store them separately in different tables?

(The text entries may be different for some of the attributes; this data would often be reported on collectively).



2. I have a Teams Table; each team would belong to a Region and then a Country with each Country having more than 1 Region. (i.e. ‘Yorkshire’ and ‘Lancashire’ would both be part of The UK…. All be it quite possibly not altogether happy about being next to one another ;-) )

Should I relate the Teams Table to a Region Table and then relate that to a Country Table? Or should I just make one relationship to a Table containing both Regions and Countries? (I'm thinking for normalization to take the first option, but am not certain)



3. I have a few tables that would link to quite simple data called Gender, it contains two options. 'Male' and 'Female'

For each table that would link to this information; should I have one Gender Table containing this data and relate them all to it, or should I create a separate Gender Table for each instance?



4. In my Players Table I have the option for each player to be carrying something in Both a ‘Left’ and ‘Right’ hand. I've created a separate field for each piece of data in the Players Table. The object being carried in each hand could be the same or different, or require both hands.

For each hand should I create a separate Objects Table containing the same data and then link to it, or is it more efficient to link both fields to the same Objects Table field?



I hope this is clear, but if not I’m more than happy to elaborate.



Thanks in advance to anyone who can help.

Kind regards,
pdjacks

1) This would depend- Answer some questions:
a) Will players have more than one same attributes, especially if you want
to keep history of past attributes?
b) Are any of attributes calculated?
c) Are attributes dependent on other data?

If you said no to all questions, then one table listing all attributes should be fine. Otherwise, you need to have few more tables, depending on what are your answers to the question above.

2) I had something similar and what I would do is, create three tables:

tblCountry
CountryID
CountryName

tblRegion
RegionID
CountryID (foreign key to tblCountry)
RegionName

tblTeam
TeamID
RegionID (foreign key to tblRegion)
TeamName

Note how CountryID is implied through Region table so you can use query if you need to know what country a given team is from.

3) Sound like you have a normalization problem if you need to link table to more than one tables.. If you're storing managers in their own table, and players in their table, then that may be why. Rather, I'd just create something like this:

tblPerson
PersonID
RoleID
GenderID
FirstName
LastName

tblRole
RoleID
RoleName

tblGender
GenderID
Gender

Notice by using roles, you now know which person is a player or manager or whatever, and only need to store gender data in that table.

The fundamental rule here is: You only should be inputting one unique record once and only one time!

4) I'm afraid I'll need more information. Please describe your present data structure then explain how the data is supposed to interact together.

Are you making a database for a D & D-esque game?

1 a) I'm not quite sure what you're asking here, so I'll try to cover what I think you could mean.

Attributes won't be the same, they will cover the same range, but with different descriptions.

Strength (1-20) 1 = Weak, 2 = Average, 3 = Strong....
Speed (1-20) 1 = Slow, 2 = Medium, 3 = Quick...

No, I wasn't planning on keeping attribute history.



1 b) Only initially as players are created. Attributes are then potentially updated as a result of experience gains, but not calculated from other data each time they're queried.
1 c) No



2) Great, that's exactly how I'd been wanting to do things.



3) It's not managers in the sense of a football club manager, but rather that I'm wanting to store data on the gender of the person (User) who logs into the tool and also the gender of the players within it.

4) Yes similar to a D & D game in the sense that you could have a player carrying:

Left - A short sword (ID1)
Right - Another short sword of the same type (ID1)


Left - A sword (ID1)
Right - A shield (ID2)


Left - A big stick (ID3)
Right - The other end of the same big stick (ID3)




Thanks very much for the help you've given me so far, it's much appreciated :-)

Now that I understand a bit better about attributes-

I'm going to assume there could be items or other things that will modify attributes either permanently or temporarily.

There's two ways to do it:

1) Create a junction table called "CharacterAttributes" between Character table and another junction table "AttributeModifier" table. All objects that can modify attributes would link to AttributeModifier with their ID with type of attributes modified and by how many. The Character table would have base attributes (which is 100% permanent) which is rolled at the creation stage. Therefore when you need a n attribute, you just query the CharacterAttribute table and do a sum function with CharacterID and attribute type as criteria.

2) Simply store attributes as a part of character table. Use Update queries to modify attributes as you progress. You may want to add a checkbox to all objects that may modify attributes then code in VBA whether the objects is picked up or dropped to run the update query. Here, tight coding will be necessary; you'll want to consolidate as much as you possibly can; I'd just go and create a custom module with public function that can be called everytime character comes into contact with anything that modifies attributes, so you just have one block of code dictating how it will be modified.

As for storing genders, I didn't realize that you wanted to store gender of a player and a character. If information about players and characters are 100% seperate, and never will be used in a query together, then there'll be no problem using one gender table to link to both user table and character table.

Now for how objects can be used on various body parts, this is toughie as you want to support using two same kind of one handed objects for both similar body parts.

Let's make a table of body parts listing every possible parts that could be used in game. Here, we keep things general; no left-handedness or rightedness; just hands, feets, fingers, etc.

Then add an junction table, call it ObjectBodyPartUse, which joins together the BodyPartID with ObjectID, indicating what body parts it uses.

Next, make another junction table, NumberBodyPartUse that joins with ObjectBodyPartUse, that will tell how many "same" body part it uses. So, one-handed sword is given a value of 1 while two-handed sword get 2.

Now, you need another table (lot of tables, already, eh?) and call it CharacterBodyPart. This is a junction table between Characters and BodyPart, telling us how many hands, fingers, etc. a given character has. The beauty is that it allows you to have 11-fingered human or three-armed freak if you wanted.

Finally, whenever a character wants to equip an item, run a query comparing the numbers of required body parts against (character's numbers of available body parts - equipped objects's numbers of body parts requirement).

You'll note that there is no discrimination of left-handedness or right-handedness here. I had assumed that at creation stage, you would specify whether a character is a lefty or not (a value stored in character table), and whenever an apporitate item is equipped, always the dominant hand first. But if handedness is that important, you'll need a way to store that information somehow. At that point, I'll just have to stop and let you figure the logical steps.

I also should point out that that body parts equipping is a first for me so I could have my proposal data structure all wrong. Second opinions is strongly advised.

HTH.

Ok, I think that's the issue with Gender's resolved too, and for that matter a similar one which cropped up this morning in wanting to assign Players to a Country/Region in addition to Teams being assigned to them.

And Left/Right handedness doesn't cause problems for me, in that it would be something I would factor in at the point where I would be rolling against the equipment used. I figure it's up to the user to get that the correct way around.




On the question of what to do with the attributes, well I hadn't actually considered mods to attributes, but since you mention it; I think both options potentially look good. The issue for me to now consider is whether and how I want any mods to be visible to the User at the point when equipment is equipped.

For my own part I think I've now got 3 possibilities.

1) The mod shows to the user directly in the attribute; in which case I like your junction table idea. (However I'm not so sure I want them to be visable as I don't like the thought that I user would move equipment about purely to see where one of there players is within a skill level... percentage based mods could lead to this, but obviously mods which are integers works regardless).

2) The mod shows in the piece of equipment, in which case it doesn't affect how I link equipment with players.

3) The mod would only be taken into account at a point where the attibute is being rolled against. And again I don't think this really effects how the tables are linked.




On the issue of numbers of hands/fingers etc and how equipment would be used within them; I've given a bit of extra thought to this, and I guess another major influencer is that a user would be purchasing equipment. Only that purchased equipment would then be available to distribute amongst the users players.

Given that this creates a limited stock of equipment for the user, does that just require that I'm adding an additional junction table (between ObjectBodyPartUse and Object ID) in order to distinguish between different instances of the same type of equipment? And is that junction table sufficient to also limit the usage of the equipment, or am I looking at also including additional junction tables to limit equipment use in the same way that I would be limiting body part use.

(It’s kind of a many to many relationship but with no duplication allowed in either direction if I’ve got my head around this the right way.)

Just a quick comment about players and teams. If players can only join team within the region they live in, and not anywhere else, then the data structure is simple- four tables.

Country -> Region -> Team -> Players.

However, if you will allow player from anywhere so the team has no region boundary, then data changes. Note that team table will not have any information about what region or country.

Country -> Region -> Players -> TeamMembers junction <- Team.

Finally, if for whatever reason you need to confine a team to given region but allow everyone from other regions, then the Team table just needs a field for RegionID as a foreign key and is linked to Region table.

As you can see, lot depends on how you want to process the data.

Regarding attributes-

1) Remember you can make attributes visible *and* not allow users to tamper with it. Furthermore, you don't have to show all; you can restrict what to show using a query.

2+3) Actually, how you structure data is important. Remember, Access cannot read anyone's mind so you need to tell it everything that is necessary to do calculations or process data or whatever. All equipments and other objects (e.g. spells, buffs, enivornment, whatever) should have information on how it could modify attributes.

You need to decide what structure will best represent the interaction among various factors.

As for equipment's availability- read this article (http://www.allenbrowne.com/AppInventory.html) about calculating stock on hand see if it helps you get idea of how you would handle this. Hint: This will have nothing to do with number of body parts used.

I'm getting some major headaches related to storing different attributes in the same table.

This is a basic version of the tables I'm using:

PlayerID (PrimaryKey)
PlayerName
PlayerStrength - Looks up from AttributeStrength
PlayerSpeed - Looks up from AttributeSpeed

AttributeID (PrimaryKey) - 1,2,3
AttributeStrength - Weak, Average, Strong
AttributeSpeed - Slow, Medium, Quick

The look up works fine in the Players table, however the problem comes when I try to query the data to see:

PlayerID
PlayerName
AttributeStrength
AttributeSpeed

As I guess is logical, both AttributeStrength and AttributeSpeed can't return different values as they're both looked up against the same (AttributeID) primary key.

I've tried adding a 2nd primary key, and relating each attribute to a separate part of a joint key, I've tried different relationship types and also no relationships. But in each case either; every value returned is at the same level, no values are returned, or every single permutation possible is returned.

To which I'm thinking to go to the more basic idea of having a different look up table for each attribute.


Unless that is you can see something that I'm missing. ;)






With the Teams/Player and different countries/regions, yes I'd be restricting teams to regions, but also allowing players to join from other regions. The main reporting requirement related to this would be based around whether players are from the team's home country/region or not.


Bit late to now, but I'm going to have a read through that stock article in the morning :-)


Thanks once again.

Exactly how do you have your attributes table and players table joined? I'm suspecting that there is only one record in the junction table which is why you're getting same key from attribute table for both field.

There's no junction table at the moment, I'm just concentrating on getting my basic view of the attributes to work firstly.

Which was:



1. I have a player table which will contain numerical values from 1-20 (Not integers) for different player attributes. The value of those attributes will be looked up and a text entry returned for any reports which would be seen by a user.

I want to do this for more than one type of attribute (i.e. Strength, Speed), is it more efficient to store all the text entries for those attributes together in one Attributes Table, or to store them separately in different tables?

(The text entries may be different for some of the attributes; this data would often be reported on collectively).




To which the links are from PlayerStrength to AttributeID, PlayerSpeed to AttributeID.... and so on.

What I'm looking for ultimately is a report which is going to give:



PlayerName - Dave
Strength - Strong
Speed - Slow

PlayerName - Jeff
Strength - Average
Speed - Quick