SQL database design question: users, items, collections

[Almo]

Hi!

We have users, and they are kept in a table. We have items, which have statistics and are kept in another table (actually a set of tables related by foreign keys).

As users play our game, they collect items, and they can have multiple copies of an item. I was thinking that we'd need a table with these columns: user_id, item_id, num.

I would then use a composite key using user_id and item_id.

Here's my question. The items are actually divided up into types: creatures, rooms, traps. As such they have completely different statistics, and are kept in different sets of tables.

This means I'll need to keep the IDs unique across types, or have seperate tables for the user's collections of different types of items. That's all fine, too. But I want to use foreign keys to keep everything clean, and I guess that means all of these things are stored in one database.

For some reason, it feels like users and their information should be kept in one DB, creatures in another, rooms in another, and traps in another.

Am I completely off base here? Should I feel fine storing all these different types of info in the same DB?

 

[CrikeyBobs]

I'd go with the same database. Much easier to deal with and, as you say, the foreign keys keep things clean. Do you envisage multiple "game" databases requiring access to a central repository of items and could the repository change mid-game and need to be reflected immediately in each game? Maybe that would be a reason for splitting it out, but even then it might be worth looking at the replication features supported by your database server.

So, IMO, same database unless you have overriding reasons not to.

 

[Almo]

I don't think we'd change things mid-game. Any changes to item stats would be done in big patches, so people couldn't log in without getting the patch to use the new data.

 

[CrikeyBobs]

This means I'll need to keep the IDs unique across types, or have seperate tables for the user's collections of different types of items. That's all fine, too.

I don't know which database server you're using, but I think you would have to go with the latter scenario for foreign keys to work.

ETA. Which I think you have already worked out.

 

[moopet]

Hi!

We have users, and they are kept in a table. We have items, which have statistics and are kept in another table (actually a set of tables related by foreign keys).

As users play our game, they collect items, and they can have multiple copies of an item. I was thinking that we'd need a table with these columns: user_id, item_id, num.

I would then use a composite key using user_id and item_id.

Here's my question. The items are actually divided up into types: creatures, rooms, traps. As such they have completely different statistics, and are kept in different sets of tables.

This means I'll need to keep the IDs unique across types, or have seperate tables for the user's collections of different types of items. That's all fine, too. But I want to use foreign keys to keep everything clean, and I guess that means all of these things are stored in one database.

For some reason, it feels like users and their information should be kept in one DB, creatures in another, rooms in another, and traps in another.

Am I completely off base here? Should I feel fine storing all these different types of info in the same DB?

I'd say either make the collection table include local_id (always a good idea), user_id, type_id, item_id and num or just, as you say, keep the id unique across types. If you set the id to be the next val of a sequence, that's pretty straightforward.

Can't see any reason to split into a different database.

 

[six7s]

As users play our game, they collect items, and they can have multiple copies of an item

Sounds to me like an ideal candidate for an object oriented approach

 

[a_unique_person]

Sounds to me like an ideal candidate for an object oriented approach

Which is the same as saying go for a different table for each object type.

 

[six7s]

No, its not

OO is (or should be) nothing like relational

 

[martu]

I don't understand why you think you may need separate databases? Any queries across databases will be slower not by much if they are on the same server but a bit.

You could create a single many to many relationship for each user-item type (which contains specific user-item type data like when they picked up the item for example), I can't see any problems with that. These tables will get big however, is database size an issue?

 

[martu]

No, its not

OO is (or should be) nothing like relational

Yes absolutely right but most SQL databases do not support OO yet.

Almo what SQL server are you using?

 

[icerat]

If the data is related at all to each other then it should be in the same DB unless there's some good reason not to. Since users are going to have items, and creatures will be in rooms etc etc, the data is clearly related and I would think single queries would be accessing multiple tables at the same time, no?

Do you have any good reason for putting the tables in separate DBs?

 

[a_unique_person]

If the data is related at all to each other then it should be in the same DB unless there's some good reason not to. Since users are going to have items, and creatures will be in rooms etc etc, the data is clearly related and I would think single queries would be accessing multiple tables at the same time, no?

Do you have any good reason for putting the tables in separate DBs?

I think he means separate tables.

 

[a_unique_person]

No, its not

OO is (or should be) nothing like relational

A database in proper normalised form will have a very similar structure, except that there is no concept of inheritance, which has never been made to work properly anyway.

 

[Almo]

No, its not

OO is (or should be) nothing like relational

We're using MySQL, so I think that's out.

I don't understand why you think you may need separate databases? Any queries across databases will be slower not by much if they are on the same server but a bit.

Just thought it might be a noob mistake to keep EVERYTHING in one DB.

These tables will get big however, is database size an issue?

I don't think size will be a problem.

Yes absolutely right but most SQL databases do not support OO yet.

Almo what SQL server are you using?

MySQL.

If the data is related at all to each other then it should be in the same DB unless there's some good reason not to. Since users are going to have items, and creatures will be in rooms etc etc, the data is clearly related and I would think single queries would be accessing multiple tables at the same time, no?

Yup, single queries will go across multiple tables.

Do you have any good reason for putting the tables in separate DBs?

Nope, no good reason. Just thought it might be bad for some reason.

I think he means separate tables.

Nope, I meant seperate DBs on the same server.

----------

Thanks for your input everyone! I'll keep it all in the same DB.

 

[martu]

Just thought it might be a noob mistake to keep EVERYTHING in one DB.

No as you can see from all the replies quite the opposite, related data should be in the same database where possible especially when you'll have queries that need to access all the data.

Good luck with it, sounds like an interesting project.

 

[Almo]

No as you can see from all the replies quite the opposite, related data should be in the same database where possible especially when you'll have queries that need to access all the data.

Good luck with it, sounds like an interesting project.

Yup, it's definitely interesting. I also cross-checked my question with the DevShed forum, and got the same answer from one of their local SQL gods.

 

[a_unique_person]

Nope, I meant seperate DBs on the same server.

----------

Thanks for your input everyone! I'll keep it all in the same DB.

You want the right amount of databases, which is as low as possible, and the right amount of tables, which should be a many as neccessary for your design.