A. The requirement is to design a Database to support a Gaming Web Site.

From: admin at corkenglishmarket.com
Sent: 13 January 2011 09:29
To: dba_requests@barryw.org
Subject: Database Schema please Help

Good Morning,

I came across your website and hands up its one of best for people like myself trying to learn
about databases and designing them, so thanks for all your info.

I am trying to create a DB that will hold info for a gaming site, I believe its simple enough but
have tried and failed.

I understand the concept the PK and FK's and well to a point about the relationships.

So a little about the schema I am trying to create. Its for a group of members who play an
online game called Lord Of Ultima,

its free and its just to hold the info of our cities with troops etc.

The information is as follows.

Its top level is World  which has 50 different Continents and on each Continent there is lots of
different cities that belong to many players in many different alliances.

An Alliance is just a group of members.

So each member in our alliance has got cities on different continents which have different amounts of
troops and resources ( wood, stone, iron and food).

The troops can increase or decrease if killed and the resources are generated depending on what level
and number of resource buildings.

The information is supplied in csv format which is exported as per the attached excel file,
this includes city related information for the resources and troops and the other cities nearby.

We insert this into a form and upload this.

It in turn is then saved in a table. With just the export all in one field.

For me to try and create simple information views for us I would like if possible to have a correct
schema which I hope you will show me how to do.

Each city can also be upgraded to a castle and then have a Palace built in it with levels from 1 to 10
and can have 1 of 8 types,
listed as follows Compassion, Honesty, Honor, Humility, Justice, Sacrifice, Spirtuality or Valour.

There can only be 1 castle/palace and 1 type per city if the palace is built in it.

I would really love to have this as a schema that I can then use php / mysql to present the views.

I have included an excel file with the fields etc.

This is only being used for a few of us to have an overview if you know what I mean and any help that
you can give would be so helpful to us.

I am trying to learn but its not easy.

If you have any further questions then please get in touch with me.

Best regards
Johnny




A. The Things of Interest are :-
   A.1 Resources
   A.2 Others, to be determined

B. How are they related ?
   B.1 To be determined



Barry Williams

January 15th. 2011

Principal Consultant

Database Answers