Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Nov 2009
    Posts
    10

    Unanswered: Database Desing -- Help Needed!

    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

  2. #2
    Join Date
    Jul 2009
    Posts
    32
    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.

  3. #3
    Join Date
    Nov 2009
    Posts
    10

    Relations

    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:

    Click image for larger version. 

Name:	relations.png 
Views:	59 
Size:	120.0 KB 
ID:	10214

    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!

  4. #4
    Join Date
    Jul 2009
    Posts
    32
    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.

    Quote Originally Posted by Frico View Post
    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:

    Click image for larger version. 

Name:	relations.png 
Views:	59 
Size:	120.0 KB 
ID:	10214

    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!

  5. #5
    Join Date
    Nov 2009
    Posts
    10

    Answer

    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!!

  6. #6
    Join Date
    Jul 2009
    Posts
    32
    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.

  7. #7
    Join Date
    Nov 2009
    Posts
    10
    Do you mean like this? And do I need to make cross referencing for every table??

    Click image for larger version. 

Name:	relations.png 
Views:	33 
Size:	36.6 KB 
ID:	10215

    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

  8. #8
    Join Date
    Jul 2009
    Posts
    32
    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.

  9. #9
    Join Date
    Nov 2009
    Posts
    10
    The family Cash and Bank money is different from the Cash and Bank money you have so I need different information.

  10. #10
    Join Date
    Jul 2009
    Posts
    32
    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.

  11. #11
    Join Date
    Nov 2009
    Posts
    10
    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?

  12. #12
    Join Date
    Jul 2009
    Posts
    32
    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.

  13. #13
    Join Date
    Nov 2009
    Posts
    10
    I made a database with cross referencing, do you have any more suggestions?

    Click image for larger version. 

Name:	db2.png 
Views:	20 
Size:	40.1 KB 
ID:	10253

  14. #14
    Join Date
    Jul 2009
    Posts
    32
    nope - looks good

    Quote Originally Posted by Frico View Post
    I made a database with cross referencing, do you have any more suggestions?

    Attachment 10252

  15. #15
    Join Date
    Nov 2009
    Posts
    10
    And for the table Grades do you think that will be useful? Because I can also make a PHP array for that.

    Quote Originally Posted by ameyer View Post
    nope - looks good

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •