Hi All!
I am Frico and I need help designing my Database!
I have a maffia-style game, I am only in the designing fase.
But I have a database and I got some questions about how to make a database as most affective as possible.

Here are some questions:

1. I got a table for members, there is a lot of information about members. What is most affective? Put everything in one table or put different kind of information in different tables?
2. Will mysql slow the server down when I have tables with a lot of information? For example: I have 1000 members, with each 100 messages. I will save each message in the database so that makes: 1000 x 100 = 100.000 items.
3. And when I save a lot of information will it use a lot of memory, because I only have limited memory. I will start off with 0.5 GB, will this be enough?

I hope you will answer soon!
Greetz,
Frico

First thing I would do is read on database normalization. That will help with number 1.

#2. You will be fine. Forums with hundreds of thousands of messages work perfectly fine on mysql. Holding the information will not slow the server at all. The queries could slow if not properly indexed, but the information is only accessed as needed, so this is not an issue.

#3 the data will technically use hard drive space. I have a forum with 5.4 million records, and it takes just over 500mb.

Hi,
I have some new questions I am now working to make the database with each Form. But I am now stuck on how to make some tables. Here I have a image about what relations and tables I already have. It's only a beginning:

1. Well you have with a maffia website always times before you can do a crime again or the time you need to spend in jail. Well do you think it is usefull to put those times in a special table or just leave it in the big member table?
   
2. For the Family table, you have members in the Family. Do you think it is necessary to make a new table for Family Members?
   
3. Do you have any tips on how to make the big Member table smaller? Or do you have other suggestions please tell me!

I hope I'll get answer as quick as possible!

It all depends on the normalization.
However. For family members. If everyone has a set number, then you dont really ned a different table. But if even one person has a different number of family members, you need a separate table for that. Especially if people can share family members.


Currently your DB setup is not relational. You don't have foreign keys to allow for cross referencing.

You have a table for cars, and a line pointing to the user. But how do you look at the user table, and associate the car with it. You need a carId in both your user table, and your car table.

Are the family members other users. or are they just part of the program.

If they are actual other users, you family setup is completely wrong. All you would need in this case is

user_id | Related_to | relation_type

related_to would set who the person was related to, and relation_type would set what kind of relation. Then that would need it's own table where you have a list of brother, dat, cousin and so on.

If the family members are not actual players of the game, disregard this.


Anyways. If you rush your DB design, the game will never function properly. It is not something that can easily be changed. It is the foundation of your house, if the foundation is faulty, the house will fall.

Well I don't need a Family Member table then, you can just search the member list with the correct FID that connects to the familyID.

But I don't quite understand what you mean that my DB setup is not relational.

For the Car Table, I think it is not necessary to put in each table the carID.
Because when I want to show the list of cars that a specific user has.
I can just search the car list with the specific userID...

I could have this wrong, but I am told that you can do this with MySQL.

And could you explain further what you mean with that the family setup is wrong?

Sorry for all my questions, but I really want to have a perfect database.
And thank you very much for all your help!!

True about being able to look at the cars table. But then you need to do one search to get the user data and a second one for the cars. You want to do it in one search

you want
SELECT * FROM users, cars WHERE cars.user_id = user.user_id AND user.user_id = 3

This will give you everything about the user including their cars.

What you have is

SELECT * FROM users WHERE user.user_id = 3

and

SELECT * FROM cars WHERE cars.user_id = 3

You want the date be be relational, and right now you basically have 5 non connected tables


But, honestly. If the SQL statement does not make sense, do your self a favor, and read 20 pages on database design, and normalization. It will take you 2 hours tops to make sense of, and will save you 40 hours of work.

Do you mean like this? And do I need to make cross referencing for every table??



And for the family setup why was this wrong? Because I didn't mean with family, specific members related to each other like uncle etc. But I meant it like a clan:P

Yeah.
I would. Its called foreign keys. It is what makes it so efficient.

For the family. If the "clan or gang" is other members. Then you dont need to store anything aside from the user_id and the relatedTo_id

You were storing other information such as their money etc. That is all stored in the user table. So you dont need it twice.

You should never need repeated information in separate tables.

The family Cash and Bank money is different from the Cash and Bank money you have so I need different information.

So in that case, you need to create a table for each gang, and store that there.

Then you have
user:
user_id | other info

gang:
gang_id | gang_name | gang_money etc

gang_members:
user_id | gang_id

This will allow people to be in more than one gang.

You would do something like

SELECT * FROM gang, user, gang_members WHERE user.user_id = gang_members.user_id AND gang_members.gang_id = gang.gang_id

It would be more complicated once someone was in more than one gang. But normalization like this will save you a ton of time later.

That's all I have for you. Go read a book on database design.
or go to the store and read the sql chapters (only 2) of this book

Amazon.com: PHP 6 and MySQL 5 for Dynamic Web Sites: Visual QuickPro Guide (9780321525994): Larry Ullman: Books

That is literally how I know what I know now. It's only like 30 pages.

Okay thanx, I will take a look at some books

And for the tblCars, the relation between fidCars and IDCars. I think it is not right because you don't have many members for only one car:P It is a one-to-many relation. Doesn't that needs to be many-to-many?

Only many to many if more than one person can have that car. If so, you should do a table for cars, and one for cars_connect that links to the users..

Also, keep in mind, some web forums have over 60 tables. So having 15 or so is not a lot.

I made a database with cross referencing, do you have any more suggestions?

nope - looks good

And for the table Grades do you think that will be useful? Because I can also make a PHP array for that.