Questioning My Database Structure...

[Custergrant 0]

Okay, I posted a brief description of this in the PHP section, but I'll summarize myself again.I'm creating a PHP based game that is an economy game, where you start out with so many resources and a building, and you expand your business empire by building more buildings, which puts out more resources, and you can buy and sell on the market and whatnot. In the end, you try and become the wealthiest player in the game.Now I already have most of my database in MySQL set-up. But I've been told several different things on my structure and wanted your guys' opinions. First, the main tables that I'm questioning are my users' tables. I have a table for resources, that holds ID, Name, Market Buy Price, and Market Sell Price, and that's no problem. Then I have a Buildings table that holds ID and Building Name. Then I have a Cost table, which is an addition to the Buildings table, and has Building ID, Cost_Type (defines whether the row information is a cost, input, or output), Resource_Type (ID of resource) and Resource_Amount (Amount of that type of Resource).Then, I have a Users table that holds ID, Username, Password, Email, and Userlevel. Now comes the part that I need help with. I have a Users_Buildings table and it just holds the user's ID from the users table, building_id, and the amount of that building. So when I call up in a script how many sawmills a particular user has, I would call up his User_ID and select all of the rows which has that ID in Users_Buildings and the Building_ID = the sawmill's ID. The same format goes for my User_Resources table, which holds a User_ID, Resource_ID, and Amount.Now a lot of people have told me I should just create a new table everytime a user registers called that user's ID and store all their building and resource information in that one table...I'm really confused as to what I should do and as I'm about to move over to this free host, I probably should change the format now if I am going to. So do any of you have any advice?

[ghostrider 0]

Hello Custergrant,

 

Firstly, let me say that you will have no regret switching to Xisto. They are excellent.

 

My name is Chris Feilbach (http://forums.xisto.com/no_longer_exists/) and I am a computer programmer. I have 11 years experience programming (started in 1st grade), and 4 years experience working with PHP and MySQL. Below are my suggestions for your database.

 

Before I give you my opinion, please make sure that all of the tables in your database are right:

 

TABLE Resource (Contains data about what resources are available in your game)

ID - Primary Key

Name - Name of resource

Market Buy Price - Price resource is sold for at market.

Market Sell Price - Price resource is bought for at market.

 

TABLE Buildings (Contains data about available buildings in your game)

ID - Primary Key

Building - Name of building

 

TABLE Cost (Contains data about resources that your buildings use or make)

ID - Primary key

Cost_type (either cost, input, or output)

Resource_Type (Refers to ID in TABLE Resource)

Resource_Amount (Amount of resource needed)

 

TABLE Users (stores your user's login and contact information)

ID - Primary Key

Username - Username of user

Password - Password of user

Email - Email of user

Userlevel - Level user has attained in your game

 

TABLE User_Buildings

ID - Primary Key

User_ID - (Refers to user ID in TABLE users)

Building_ID - (Refers to ID in TABLE buildings)

Amount - Number of buildings user owns

 

TABLE User_Resources

ID - Primary Key

User_ID - (Refers to user ID in TABLE users)

Resource_ID - (Refers to ID in TABLE resources).

Amount - Number of this resource that the user owns

 

Granted that this information is correct, here are my recommendations.

 

1. As a general rule of thumb when using MySQL, stay uniform. If you have a table for users and resources and buildings, make sure that their names are either singular (user, resource, building) or plural (users, resources, buildings). TRUST ME, it gets very complicated once you start working with around 10+ tables and some of them end in s, and others do not. I leave all of my table name singular.

 

2. Make sure also that all of your field names are lowercase (saves your time). You want this to be as easy as possible for yourself to code.

 

3. Make sure that all of your id fields have auto_increment set (once again, saves time).

 

4. After reviewing your code, you absolutely NEED to combine the tables Buildings and Cost. If you happen to delete one row from buildings, things will go horribly wrong.

 

5. Keeping User_Buildings is great, but it offers one major limitation. Let's say someday you grant your user's the opportunity to improve upon their buildings, or even if you want to be able to customize individual buildings, or anything that involves modifying one building. Your current design does not allow for that. Below is a design of how you can address this.

 

TABLE user_building

id - Primary key

building_id - id of building

user_id - id of user who owns the building

(any other custom fields may be added)

 

In order to keep a count of the buildings you can use the following code:

 

$query = mysql_query("select count('id) from user_building where user_id='__USER_ID_HERE__' and building_id='__BUILDING_ID_HERE__'");$result = mysql_fetch_array($query, MYSQL_NUM);print $result[0]; // Prints number of buildings.

I really hope this helps you. All of my contact information is located in my Profile on this forum.

[Custergrant 0]

Hey, thanks for the reply, I'm probably going to contact you here in a minute. For the sake of making this post more readable, I capitalized all of my fields and tables, they're all lower-case and plural at the moment. :DI hear you completely on the buildings and cost tables. The big problem is, it would cause duplicate entries, because one building may cost 100 Stone, 100 Lumber, and 100 Coin, unless I make an extra ID field with auto inc and then just make sure every row that includes those cost links up to its proper building_id...Okay, so you think I should keep user_resources and user_buildings seperated and not make just one table for every user...and I hear you on the modification part, but I really can't see any modifications at the moment, else, it would have to update the building's input and output...which would require even more work.

[Custergrant 0]

Sorry for the double post..

Chris, I just redid my buildings table and I'm going to try to explain the 'useless' ID field that I was talking about on MSN.

First, I agreed that I just need one table for buildings, so I just kept the buildings table with the fields of id, building_id, name, cost_type, cost_restype, and cost_amount. I thought about just making a field for cost type, cost amount, input type, input amount, and so on, but it would really cause some duplication problems, and the cost_type system really makes it easier, because you can simply give it a number (0, 1, or 2) and that defines cost, input, or output.

So my create table code looked like this:

CREATE TABLE buildings(	id INT AUTO_INCREMENT PRIMARY KEY,	building_id INT,	name VARCHAR(255)	cost_type INT(1),	cost_restype INT,	cost_amount INT);

Notice the first field of id, it's auto_inc and also the primary key, and it's also a completely useless field in terms of data, but it's the key to making these types of tables fit together.

Anyways, I'll explain its role after I explain the actual data insertion:

-- Iron Mine, Cost, 500 Lumber 1000 Stone 200 Coin, Produces 25 IronINSERT INTO buildings (building_id, name, cost_type, cost_restype, cost_amount)	VALUES ('7', 'Iron Mine', '0', '8', '500');INSERT INTO buildings (building_id, cost_type, cost_restype, cost_amount)	VALUES ('7', '0', '24', '200');INSERT INTO buildings (building_id, cost_type, cost_restype, cost_amount)	VALUES ('7', '0', '2', '1000');INSERT INTO buildings (building_id, cost_type, cost_restype, cost_amount)	VALUES ('7', '2', '7', '25');

That's a typical insertion command for one of my buildings. I have the comment with the name and statistics just for quick reference on my part. So on the first line, I'm inserting the building_id, name, and so forth. The building_id for this building is '7', which is consistent throughout this. I inserted the name, and a 0 for the cost, and an 8 for a resource type (Lumber) and an amount, 500. Now I can move onto the 1000 Stone, and instead of having to add extra fields or face duplication error, I just make another row, and it won't duplicate because the field 'id' is the primary key and it's on auto_inc, so I can simply put in the building_id to match it back up to it's proper building and keep on inserting the data for that table.

And I've actually used this 'useless id' field in A LOT of my tables, because there is usually more than one line of data for the stuff I'm putting data in for, and instead of making these huge complex tables, just make some extra rows and it's all there. All you have to do is select the data where the 'real' id = whatever.

I'm not sure if most people do this when they make MySQL tables, but I was pretty well new to it and this really made a lot of sense to me and makes it much easier to store data.

But Chris, is this a better layout for the buildings?

[ghostrider 0]

I see what you mean now. Your format for your tables is just fine .Keep in touch, I'd like to hear about how your project is coming along.