MySQL Database Design

[wjohn]

users

id

username VARCHAR 16

password VARCHAR 40

userlevel (standard 0 = not verified e-mail) TINYINT 1

email VARCHAR ??

 

world

id

uid = User ID

x Coordinat

y Coordinat

 

This is my simple mysql database design I'm on so far and we can summarize this simply that you can log in with username, password and can then create a "Base" that coordinates will be saved in the "world" table. The thing is that the base should be able to save buildings that have a certain level. How is the best way to make this? I would assume it's to make a new table and then call it like "buildings" And have some columns like these:

 

id

uid

name

description

level

Now let's say that I would like to see that you don't cross 16 buildings per base, but you should be able to add more bases in future, so I guess I need to add the x, and y coordinat to KNOW what base you are upgrading, right?

 

"BUILDS TABLE"

 

id

uid

level

x

y

slot (WHAT SLOT IT IS, WHEN IM DRAWING THE BASE IN PHP)

 

"BUILDINGS TABLE"

 

id

name

description

 

Now I would like to add features on the buildings, let's say you can store resources in a building, that you get every hour (Cron job) How would I add that in to my database design?

[falkencreative]

Assuming I understand things correctly...

 

Looks like you have things about right. I'd be doing something like this:

 

DB Tables:

- World (world id)

- Bases (includes a reference to the id of the world the base is in, and the base id)

- Buildings (includes a reference to the base id, and the id of the building status)

- Building Statuses (status id, information about the building status)

 

Say your building has a status that allows the user to withdraw X resources per hour. You could use a CRON job to call a PHP script that uses MySQL to check the specific building's status, and take actions based on the status.

[wjohn]

Could you show in an example the Building Statuses table?

[falkencreative]

Could you show in an example the Building Statuses table?

You shouldn't need to do anything fancy. All of the logic would need to be done in the PHP script, not in the database. All the database table needs to hold is a unique id per building status, and a description. For example:

 

ID: 1

Status: "Receive 5 resources per hour."

 

Then in your PHP script, you could get the ID of the building's status from the database using a simple if statement:

 

$status = [get status from Ddatabase here...]
if ($status == 1)
{
   // provide user with 5 resources every time script is run
}

[wjohn]

Lastly, Just so I get hang of it, If I would to set a certain level of buildings, ex you can upgrade it, would I put the column "Level" in buildings then? And finally, If I would to put restrictions, you need Building A to build Building B, `should I just do that with plain PHP?

[falkencreative]

Lastly, Just so I get hang of it, If I would to set a certain level of buildings, ex you can upgrade it, would I put the column "Level" in buildings then? And finally, If I would to put restrictions, you need Building A to build Building B, `should I just do that with plain PHP?

Sounds correct to me.