hi help on tables please

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
reecec
Forum Contributor
Posts: 218
Joined: Sun Apr 02, 2006 7:12 am

hi help on tables please

Post by reecec »

hi i need some help for my online game again please


im not sure how to do this would i have one database


and have a table for cars,user stats,weapons,shields


and when showing them would i jus select rows from the current user to dispay eg. cars from cars table




or would i put it all in one table

or more the one database

please advise thanks
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: hi help on tables please

Post by RobertGonzalez »

reecec wrote:... and have a table for cars,user stats,weapons,shields...
One database, multiple tables...

Car Table
car_id
car_name

Weapons Table
weapon_id
weapon_name

Shields Table
shield_id
shield_name

User Table
user_id
user_name
user_car_id
user_weapon_id
user_shield_id

Of course this assumes that each user is only allowed one of each item. If they are allowed more, then you can create joining tables (that's what I call them, not really sure what the technical term is) where you would have two primary keys in a table for user_weapons (user_id, weapon_id), user_cars (user_id, car_id) and user_shields (user_id, shield_id) then do a series of join select queries to pull it all together into an array for use throughout the script.

There are probably better ways to do this, but this is one possibility.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Re: hi help on tables please

Post by GM »

reecec wrote:hi i need some help for my online game again please


im not sure how to do this would i have one database


and have a table for cars,user stats,weapons,shields


and when showing them would i jus select rows from the current user to dispay eg. cars from cars table




or would i put it all in one table

or more the one database

please advise thanks
Usually, you use a table in a database to contain information on a specific thing. It makes no sense (and indeed would be very difficult) to put information about cars, weapons and shields all in the same table. To follow database normalisation rules, you should never have more than one item of information in a single database field, and you should not have redundant information. This means that a solid database design usually comes from an implementation like Everah's above, where you have a table for "Cars", which contains the Car's ID (each car has an ID which is unique to that car), the Car's description, and other information about the car (speed, colour, etc). In the rest of the database, when you refer to a car you always use it's ID. Never the description. For example, if user A has a Ferrari, and Ferrari has ID 39 (for example), your table that links the user to the car would look like:

Code: Select all

T_USER_CAR
ID_USER      ID_CAR
-------------------------
     A                39
To find out the model of the car, you would join this table to the Cars "Master" table, using the ID_CAR as the joining field:

Code: Select all

SELECT U.ID_USER, C.CAR_MODEL FROM T_USER_CAR U, T_CARS C
WHERE U.ID_USER='$user'
AND U.ID_CAR = C.ID_CAR
Usually, if you have a "1 to Many" relationship (1 user can have many cars), you typically need 3 tables - one for the Users, one for the Cars, and one for the User-Car relationship.

In a 1 to 1 relationship (one user can only have one car), you can use 2 tables, one for Users and one for Cars, and you can hold the ID_CAR in a field in the Users table, but note that if (at a later date) you want to allow users to have more than one car you will need to change the database design, and (probably) a lot of code.

I'm of the opinion that easily maintainable code stems from a solid foundation, and usually a big part of the foundation is the database.
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Moved to databases...
Post Reply