Jump to content
  • Advertisement
Sign in to follow this  
OBallard

Database designs

This topic is 2587 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Ok, so I'm designing a remake of an online text browser game I used to play..
I figured, now that I've got the main basis of the site layed out, that it would be a good idea to make a start on the database design..
I've got one set up already, for people that register.. The fields are: ID, Permissions, email, login, password, Empire name, race & referrer.

A quick run down of the game, is that you build buildings, to make money/resources (The resources being Money, Food, People, Orium & Argentum..), to buy military, to buy / steal planets (from attacking). You also have research that can increase fertility, gain resources quicker, etc..

To get a better overview (or if you're just interested), it would be a good idea to browse the guide taken from the wayback machine.. (the game stopped running back in 2006)

EDIT IN: Saying that, here's a picture of the main page once you've logged in.. Everything in that table will end up in a database. I just can't seem to think of the best way to go about it..

I was wondering if anyone would give me any tips on the database design.. How should I make it??
Thanks

Share this post


Link to post
Share on other sites
Advertisement
well i can help, but i won't be willing to build you the whole db. if you have any specific question about db design, feel free to PM me.

Share this post


Link to post
Share on other sites
Good Database design is not quite as easy as it looks like at first glance.

The first thing to remember is that data is stored once and only once. Now that may seem fairly simple but you would be surprised how easy it is to break that rule.

Second thing, figure out ALL the data you need to start before you start the design. A lot harder then you might think. A simple addition of one can change the design of multiple tables.

Store only the information that can not be calculated. If you have information that you display that can be calculated by values in the database, make sure you store the values used for the calculations, not the end result.

Avoid many-to-many relationship. They are EVIL:. One-to-many are good.

One-to-one relationships are just fine but you should evaluate why you are using them. Can that information be contained in one table or do you really need two or more tables.

If you have questions about this just ask. Start figuring out what you want to store and how.

Share this post


Link to post
Share on other sites

...
Avoid many-to-many relationship. They are EVIL:. One-to-many are good.

One-to-one relationships are just fine but you should evaluate why you are using them. Can that information be contained in one table or do you really need two or more tables.
...


Could elaborate a bit on this?

Share this post


Link to post
Share on other sites

Good Database design is not quite as easy as it looks like at first glance.

The first thing to remember is that data is stored once and only once. Now that may seem fairly simple but you would be surprised how easy it is to break that rule.

Second thing, figure out ALL the data you need to start before you start the design. A lot harder then you might think. A simple addition of one can change the design of multiple tables.

Store only the information that can not be calculated. If you have information that you display that can be calculated by values in the database, make sure you store the values used for the calculations, not the end result.

Avoid many-to-many relationship. They are EVIL:. One-to-many are good.

One-to-one relationships are just fine but you should evaluate why you are using them. Can that information be contained in one table or do you really need two or more tables.

If you have questions about this just ask. Start figuring out what you want to store and how.


So, when you say that data is stored only once, then no multiple fields for the same thing? For example, you build a building that produces a certain resource. Attacking someone to get a planet also adds to that resource. Rather than having the resource in all three tables (resource, planets and buildings), I would only have it in the one area.. Right? Would that include a player's ID? Or is that the accepted case in this..
Yeah, I thought that entering all of the data in the first time would be the best way to go about this, this is one of the main points I was hoping someone would suggest, as it would mean I'd take more care in including everything the first time round. However, if there was something i needed to add at a later date - for example, a new addition to the game - how badly would this impact on the table designs? Would it be a major crisis or would it be a simple case of making a new table and adding in the php to interact in the pages needed?
I think I get what you mean about the information that cannot be calculated.. However, could you explain why? I'm guessing something like networth would be something that could be calculated (as it draws upon every part of the game.. Buildings give networth, as do resources, military, planets, etc.. ) but why shouldn't I have it in a table?
I also don't understand what you mean with the relationship term.. Please could you explain a little.


@ DpakoH, thanks, I'll probably have to take you up on that, if that's ok?

Thanks for the help, it's appreciated. :)

Share this post


Link to post
Share on other sites

[quote name='TheTroll' timestamp='1305117862' post='4809390']
...
Avoid many-to-many relationship. They are EVIL:. One-to-many are good.

One-to-one relationships are just fine but you should evaluate why you are using them. Can that information be contained in one table or do you really need two or more tables.
...


Could elaborate a bit on this?
[/quote]

Were you looking for definitions of many-to-many and one-to-one? Or were you looking for why (they are evil/good, etc.)?

Share this post


Link to post
Share on other sites

[quote name='Burnt_Fyr' timestamp='1305134266' post='4809498']
[quote name='TheTroll' timestamp='1305117862' post='4809390']
...
Avoid many-to-many relationship. They are EVIL:. One-to-many are good.

One-to-one relationships are just fine but you should evaluate why you are using them. Can that information be contained in one table or do you really need two or more tables.
...


Could elaborate a bit on this?
[/quote]

Were you looking for definitions of many-to-many and one-to-one? Or were you looking for why (they are evil/good, etc.)?
[/quote]

Both. At least, I am.

Share this post


Link to post
Share on other sites
Sorry it took so long to get back to you, had a long day yesterday.

One-to-one relationship are most likely the most rare of them all. It means that for each record there is only one other record matching it. So, it seems like you could just put them all in one table, and that is true, you could. So why do it? It is really only used for a table that you use a lot. But let's say you only use a few fields in this table and the rest only get use rarely. So, you would put the few records in one table to use all the time and the other records in another table to use when you need them. So this will help the speed of the one table that you need to use all the time.

Many-to-many relationships are very important to database programming. But didn't I say they were evil? Yes I did, mainly because people new to DB work will mess them up and make things work very very slowly. So if possible avoid them because unless done correctly they can but huge performance hits. So a many to many relations ship is where you have two tables and each row in one table can reference multiple rows in the other table. A good example would be a school. You have a table of teachers and the students they teach, so each teacher can teach multiple students, then in the students table you have the teachers that teach them, each student can be taught by multiple teachers.

So why do we not store the results of calculations? Because it is SLOW!!!!. In your application fetching data is the slowest thing you can possibly do. So don't do it unless you have to.

Okay, so let's some basic design, I have no idea how you are really going to do it so this is all just guesses.

So first we have the "Player" table.
ID, Player Name, password one-way-hash. Amount of resource A, Amount of resource B, Amount of Resource C, etc.]

Then you have a "Building" table,
ID, Building Type ID, Player ID, Level of Building.

Resource Table
ID, Resource Type ID, Player ID, Level Of Resource.

Hope that makes sense.

Share this post


Link to post
Share on other sites
With the examples you have given, you have put the resources in with the players table, but also have a separate table for resources. Is there a particular reason for doing this? Would that be the same for military and buildings? Ie, placing the units of each into the players table but having a separate one for the identification of the units?
Or would it make more sense to have the units, along with players networth, amount of planets, etc, in a completely separate "player table".?

Also, as you advise not to use calculations in tables, something like networth ( points allocated for use in ranking ) would be done in php, drawing the initial units networth 'cost' x by the amount each player owns?
For example, in the buildings table, it will have the Id, name, networth value for one building. And in the table that holds user data, you have 500 of a certain building, would you just x the two together, even though they're from different tables?

Also, I'm guessing the reason for having the player Id field is to identify who each part is for?

Unless I'm misunderstanding you, I think the majority of the tables will have one to one data, apart from the networth fr example. I might be completely wrong though. Haha

Thanks for the answers though, it's appreciated... Although sorry I'm not quite getting it all yet, I'm trying to understand why for everything.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

Participate in the game development conversation and more when you create an account on GameDev.net!

Sign me up!