14 Replies - 398 Views - Last Post: 02 July 2013 - 12:00 PM Rate Topic: -----

#1 qwigoqwaga  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 02-July 13

Advice on Database Design Wanted

Posted 02 July 2013 - 06:31 AM

Ok so I just joined here since I've been having trouble getting a clear answer elsewhere.

I'm working on developing essentially a virtual pet game (for those unfamiliar, think like Neopets) and I've been trying to plan out my database. There are a few things I'm not totally sure how I want to do or what the best way to do them is and I want to make sure the database is set up as cleanly and correctly as possible.

Sorry if this is long and confusing but if you could help me out with at least part of it that'd be awesome ^^

So then, here's what I've got for the tables in it and under them the columns in the tables:

users:
-user_id (autonumber, primary)
-login (unique)
-email (unique)
-username
-password
-salt
-upgrade (0-basic, 1-upgrade)
-expiration
-avatar
-joindate
-money
-brains (currency)
-camp (profile description)
-settings (not totally sure what to do with this...)
-links (another setting)

After talking to some people I think this is the best way to go about things like bans and staff with a permissions table, most of the columns are different kinds of restrictions and the different kinds of staff would simply get listed as whichever they are.
To try and not make a big redundant table or stick this all in the users table this table would hold each time someone gets changed to anything other than the default regular user permission settings, it would make a record here that way I don't have to list every user since the majority will be default.

permissions:
-user (id, primary)
-logdate (timestamp)
-staff
-arrest
-probation
-chat
-forum
-message
-suspension
-ban
-comment

I should probably explain then that this is a zombie sim where basically you keep zombies as pets.

zombies:
-zombie_id (autonumber, primary)
-zombie_name
-zombie_description
-zombie_owner (id)
-zombie_level (number)
-zombie_type (0-special, 1-custom)
-brains (number)

The above table is pretty simply. Those are your pet zombies.
But the below one I'm still struggling a bit with. See because along with normal zombies as pets, since people will likely end up with way more than would be practical to have on a page or make pages for, there would also be zombies that are essentially numbers. Like on someone's profile they'd have links to all the above normal zombies, but also it would list something like "this person has 28 level 2 zombies, 7 level 3 zombies, 18 level 5 zombies, etc." and I'm really not sure how to do that in a way that connects the user to the number of zombies at each level they have without making a massive table.

number_zombies:
-group_id (autonumber, primary)
-owner (id)
-level (number)
-number (number)

The itemslist will simply be all the items and then useritems are the ones players own

itemslist:
-item_id (autonumber, primary)
-item_name (unique)
-item_type
-item_description

useritems:
-item_id (autonumber, primary)
-item_owner (id)

I think I've got the forums fairly figured out.

forums:
-forum_id (autonumber, primary)
-forum_name (unique)
-forum_description

topics:
-topic_id (autonumber, primary)
-forum (id)
-user (id)
-title
-content
-date

posts:
-post_id (autonumber, primary)
-topic (id)
-forum (id)
-user (id)
-post
-date

chat:
-id (autonumber, primary)
-user (id)
-message

Except maybe this part. I'm trying to figure out the best way to allow users to edit their posts while still having the original versions exist.

archive:
-date (primary)
-user (id)
-post (id)
-topic (id)
-forum (id)
-title
-content

Is This A Good Question/Topic? 0
  • +

Replies To: Advice on Database Design Wanted

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9393
  • View blog
  • Posts: 35,273
  • Joined: 12-June 08

Re: Advice on Database Design Wanted

Posted 02 July 2013 - 07:02 AM

Quote

Like on someone's profile they'd have links to all the above normal zombies, but also it would list something like "this person has 28 level 2 zombies, 7 level 3 zombies, 18 level 5 zombies, etc." and I'm really not sure how to do that in a way that connects the user to the number of zombies at each level they have without making a massive table.


That's why the good Lord provided SELECT statements. I am guessing you have a table to make the relation between a zombie_id and a user_id, right? A user can have zero to many zombies, while a zombie can have only one user.

You would use that table in a join... so it would go from teh user table to the user_zombie relationship table to the zombieid table.

The same sort of relationship table woudl be setup with the item list. Figure if there needs to be unique items that only one id can ever be assigned to a user, or if these are just generics so multiple users can have the same item, etc.



Quote

Except maybe this part. I'm trying to figure out the best way to allow users to edit their posts while still having the original versions exist.

If you want to archive posts then I would suggest having an 'archive' table. A user selects a post to be edited, makes an edit, and clicks the 'save' button. The save button pushes the current post (the one being edited from the 'posts' table) on to the archive table. Then it updates the 'posts' table entry with the edited text.
Was This Post Helpful? 0
  • +
  • -

#3 qwigoqwaga  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 02-July 13

Re: Advice on Database Design Wanted

Posted 02 July 2013 - 07:15 AM

No?
The zombie owner would be the user's id.
Trying to wrap my brain around this a bit...so then would I need to make an entirely separate table for that? Wouldn't that be redundant or am I understanding that wrong?

Ok at least I was going the right way with the archives thing then, thanks ^^
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9393
  • View blog
  • Posts: 35,273
  • Joined: 12-June 08

Re: Advice on Database Design Wanted

Posted 02 July 2013 - 07:17 AM

That's why it is called relational data. You have two data stores and you need to relate them. That typically happens in a third table.

http://www.dreaminco...-normalization/
http://msdn.microsof...fice.11%29.aspx
Was This Post Helpful? 1
  • +
  • -

#5 qwigoqwaga  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 02-July 13

Re: Advice on Database Design Wanted

Posted 02 July 2013 - 07:39 AM

That first link was especially helpful. I tend to think more visually so ow that I see the diagrams I understand what you mean ^^

I don't think I'd really need a third table for the regular zombies though since the owner's id would be a foreign key to the user.
But the numbered zombies....I dunno. I'm trying to think of how I could do that...
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9393
  • View blog
  • Posts: 35,273
  • Joined: 12-June 08

Re: Advice on Database Design Wanted

Posted 02 July 2013 - 08:20 AM

Quote

I don't think I'd really need a third table for the regular zombies though since the owner's id would be a foreign key to the user.

I assume I don't understand what that means because I am lacking game specific knowledge, but okay.

Quote

But the numbered zombies....I dunno. I'm trying to think of how I could do that...

How to do *what*?
Was This Post Helpful? 0
  • +
  • -

#7 qwigoqwaga  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 02-July 13

Re: Advice on Database Design Wanted

Posted 02 July 2013 - 08:58 AM

Sorry this is confusing ^^'

Let me try and explain again/better.

So basically you own zombies. Some of them are like a normal pet site where each zombie has an id, profile, image, name, etc.
Since you can potentially have so many of them though, you probably don't actually want a page/image/name/etc. for each of them. So you'd have the normal ones you want and then your army camp (profile :P) would just list the rest as like Level 2 Zombies 16, Level 7 Zombies: 43, Level 8 Zombies: 100, etc. and those "non-special" ones would just be numbered like that. You'd have to set them to be the regular kind with a profile and all when you first get them otherwise they're automatically a number.
Each new zombie starts at like level 0 and you have to teach it how to be a zombie and once it has the basics taught individually your whole army will "learn" and gain levels as a group (not like all to level 10 or whatever but each +1 to whatever it currently is) whenever you do certain things (grave rob, go on zombie walks, hang out in a cemetery, talk to spirits, related "zombie things" XD).

So what I'm trying to figure out is how to store those numbered zombies in the database without having to have a level cap and list out all the levels and then who has how many zombies of each level.

Does that make more sense?
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9393
  • View blog
  • Posts: 35,273
  • Joined: 12-June 08

Re: Advice on Database Design Wanted

Posted 02 July 2013 - 09:18 AM

Sure.. but regardless if they are not flagged as a 'special zombie' I would advocate _every_ zombie has a unique id in the table.. and a bit flag for the unique status or not. If only one person can own a zombie then sure.. no relationship needed.. but if you plan on having joint ownership then I would think about that relationship table.

Example:

table #user(id int, name varchar(50))
id          name
----------- --------------------------------------------------
1           Bob
2           Alice
3           Chad
4           Dave



table #zombie (id int, userid int, level int, special bit)
id          userid      level       special
----------- ----------- ----------- -------
1           1           10          0
2           1           1           0
3           2           1           0
4           4           10          1
5           3           10          0
6           1           2           1
7           1           1           1
8           1           1           0



if I want the special and non special count of zombies for user 1:

Quote

declare @userID INT
set @userID = 1


select b.*
from #user a with(nolock)
join #zombie b with(nolock)
  on a.id = b.userid
where a.id = @userID
and b.special = 1


All my special zombies.. so they are enumerated out..
id          userid      level       special
----------- ----------- ----------- -------
6           1           2           1
7           1           1           1



The non special I just group by their level and find their count:

select level, count(b.id) as howMany
from #user a with(nolock)
join #zombie b with(nolock)
  on a.id = b.userid
where a.id = @userID
and b.special = 0
group by level


level       howMany
----------- -----------
1           2
10          1


Both require me to join two tables together on a common key (the user uid), but both take a separate bent when dealing with aggregates.
Was This Post Helpful? 0
  • +
  • -

#9 qwigoqwaga  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 02-July 13

Re: Advice on Database Design Wanted

Posted 02 July 2013 - 10:31 AM

I feel like I'd have a monster table if every zombie had an id. I mean I'll have to do it that way if I can't find a better way, but I'd rather not.
I want to be able to just tell the database player A has X number of level 2 zombies and Y number of level 5 zombies and so on and then be able to change that so when they level up or get new zombies I can just say ok now player A has Z number of level 2 zombies and Y number of level 6 zombies.
But I don't know if it's possible to save it quite like that.
The closest I could come up with was to consider Player A's level 2 zombies a group. Which is the group_id. Then I can connect that to the owner's id and keep track of how many zombies are in the group and what level that group is, meaning there'd have to be a group for each level someone has. Would that work?

And then supposing I can't make it work, do you think it would cause much of an issue to just have every zombie have an id?

There shouldn't be joint ownership. Players A and B shouldn't be able to both own zombie C.
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9393
  • View blog
  • Posts: 35,273
  • Joined: 12-June 08

Re: Advice on Database Design Wanted

Posted 02 July 2013 - 10:54 AM

Quote

I feel like I'd have a monster table if every zombie had an id.

... and?

If anything then your log files would be 100% complete.


Quote

I mean I'll have to do it that way if I can't find a better way, but I'd rather not.
I want to be able to just tell the database player A has X number of level 2 zombies and Y number of level 5 zombies and so on and then be able to change that so when they level up or get new zombies I can just say ok now player A has Z number of level 2 zombies and Y number of level 6 zombies.
But I don't know if it's possible to save it quite like that.

Okay.. so what's the function of zombie in a game? Do they "battle"? Do they "level up"? those are _unique_ actions that require a _unique_ subset, right? If given zombie level 1 moves to leve 2 then it would make sense to update a specific record and not have some sort of pseudo data table that holds a player id, a zombie level, and a count of how many zombies are in said level.



Quote

And then supposing I can't make it work, do you think it would cause much of an issue to just have every zombie have an id?


You already have my opinion on that.. logs, complete data, easy of transitioning a zombie from nothing to something, etc.
Was This Post Helpful? 0
  • +
  • -

#11 qwigoqwaga  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 02-July 13

Re: Advice on Database Design Wanted

Posted 02 July 2013 - 11:10 AM

I'm just worried about how much having so many giant tables will slow the site down. I want to cut out unecessary stuff idf I can.

Kind of.
I was thinking as far as leveling up instead of doing each individual zombie has their own experience meter and levels up independently, they'd all just level up together.
So like you as a user have one experience bar and when you fill it basically every single zombie of yours just goes up a level.
Was This Post Helpful? 0
  • +
  • -

#12 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9393
  • View blog
  • Posts: 35,273
  • Joined: 12-June 08

Re: Advice on Database Design Wanted

Posted 02 July 2013 - 11:20 AM

Quote

I'm just worried about how much having so many giant tables will slow the site down. I want to cut out unecessary stuff idf I can.

Look - having a healthy long view of table size is one thing, but letting that curtail functional f'n options is just silly. You have no idea what your queries will be like, server speeds, sizes, usage, etc... if you have a viable option then go for it now and then cut back later... when you know what truly (and honestly) needs to be cut.

Quote

I was thinking as far as leveling up instead of doing each individual zombie has their own experience meter and levels up independently, they'd all just level up together.

What evs.. it's your game and your mechanics.. you work that out.


Quote

So like you as a user have one experience bar and when you fill it basically every single zombie of yours just goes up a level.

I do not have a full context on the game, or what the zombies are actually used for (putting pretty pretty dresses on them? Pokemon battles? Gardening? flash mob dance choreography? etc), but on face that game mechanic makes zero sense and sounds horrible. Then again that's to me.. this guy.. on the outside. These are things you need to work out *BEFORE* you start coding. Lord knows programming for a moving target sucks something fierce.
Was This Post Helpful? 0
  • +
  • -

#13 qwigoqwaga  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 02-July 13

Re: Advice on Database Design Wanted

Posted 02 July 2013 - 11:42 AM

Oh trust me I'm no where near ready to start coding any of that stuff yet.
That's why I'm planning it now. ^^'

I've got like a 14 page concept document and tons of txt files with various ideas on top of that. If you want I even have most of the original idea (since added to and edited) posted on another site, but I'm not familiar with outside linking rules here ^^'
At the basic level the point of the game is to make zombies and then take over the world by sending your zombies on raids for territory. Leveling up will help determine how well your zombies will do and therefore how often you win territory, convert new zombies, etc.. Having them all level up together isn't terribly ideal or conventional, but for the number zombies at least it would seem confusing if they would level up but you would never know which or when. I have been considering treating the special zombies like pets though and giving them each their own experience meters.
And yes you can dress up the special zombies that have profiles and all XD

XD also I should totally have flash mobs now
Was This Post Helpful? 0
  • +
  • -

#14 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9393
  • View blog
  • Posts: 35,273
  • Joined: 12-June 08

Re: Advice on Database Design Wanted

Posted 02 July 2013 - 11:50 AM

Not really how I view zombies.. I mean there is _no_ experience to be learned, gained, etc (unless you are Romero's "Land of the Dead").. so at worst a zombie in battle looses chunks of itself.. at best has minimal damage for another day.. but that's immaterial to your game.

Good luck with it.
Was This Post Helpful? 0
  • +
  • -

#15 qwigoqwaga  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 02-July 13

Re: Advice on Database Design Wanted

Posted 02 July 2013 - 12:00 PM

I'm not terribly familiar with common zombie culture. Honestly the idea started as somewhat of a satire where you just own zombies as pets. I wanted to be able to have fun with that. So the zombies have to be able to learn to an extent at least how to zombie and go on raids XD
So I also plan to have zombies that you can give brains to and it makes them smarter and eventually they actually can learn things.

And I do apologize if I came off as argumentative at all because I tend to do that when I want to understand something. And also I'm glad you're challenging my ideas because that's how ideas happen and how I can see problems in my concept ^^ So thank you.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1