jQuery in Action, 2nd edition*
The moose likes JDBC and the fly likes Architecture for online community Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Architecture for online community" Watch "Architecture for online community" New topic
Author

Architecture for online community

Daniel Prene
Ranch Hand

Joined: Jul 15, 2005
Posts: 241
I'm designing a java powered online community backed by mysql. I'm having alot of fun and have made alot of progress with the back end java code, but am running into some dificulties with designing the database structure... I want to make sure I get things right and make sure I'm not reinventing the wheel.. I want to support forums, private messages, propagation to updating of avatars, profiles, comments, and all that other great stuff. Any insight on how to accomplish this would be endlessly appreciated...

This is what I have planned out so far:
All messages are stored in the same table with feilds representing where they belong. The same goes for comments, posts, and every other collection of items including users. Is that the best way to do it?

Thank you for atleast reading this post,

-D.P.
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Originally posted by Daniel Prene:
I'm designing a java powered online community backed by mysql. I'm having alot of fun and have made alot of progress with the back end java code, but am running into some dificulties with designing the database structure... I want to make sure I get things right and make sure I'm not reinventing the wheel..

You ARE reinventing the wheel. There are lots of forum software choices out there. If you want to do another one that's fine... but it has been done before.
Originally posted by Daniel Prene:

This is what I have planned out so far:
All messages are stored in the same table with feilds representing where they belong. The same goes for comments, posts, and every other collection of items including users. Is that the best way to do it?
-D.P.

Your description is a bit vague but I am going to say yes.

HOWEVER... my experience with MySQL makes me want to suggest to you that you plan on having archive type tables of older posts, messages, etc that are essentially provided for read only purposes. I would stick things that are older than a certain time (let's say 1 month) into the archives.

I have had alot of experience with MySQL including as a moderator/admin for another board and I am really really really unimpressed by MySQL's ability to deal with large tables. The post table for the forum I work on has about 220,000 posts and it crashes the MySQL database a few times a week. The software in that case is PHP based but it is a popular commercial forum software product and my extensive debugging has demonstrated to me at least that for largish tables MySQL leaks memory very badly, especially on updates.

Anyway back to your case. I would be very wary of choosing MySQL at all but if you must use it please plan to be able to archive older posts or messages or even delete them. Once you get to a certain size if you are unable to do this your program will not work.
Daniel Prene
Ranch Hand

Joined: Jul 15, 2005
Posts: 241
You ARE reinventing the wheel. There are lots of forum software choices out there. If you want to do another one that's fine... but it has been done before.

It's not really a forum, but that part of the backend will be similar. I wouldn't be doing this if I hadn't come up with a new angle.

What do you suggust for a backing database then? My freind said postgresql...

Is there a way to reference other feilds?
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Originally posted by Daniel Prene:

It's not really a forum, but that part of the backend will be similar. I wouldn't be doing this if I hadn't come up with a new angle.

What do you suggust for a backing database then? My freind said postgresql...

Is there a way to reference other feilds?


I haven't used postgres too much. (very little in fact) If you are asking me for a recommendation I would have to say hands down based on my experience MS SQL Server. But I don't know if that's an option for you.

Honestly the fact is it shouldn't matter too much what DB you choose. All I am saying is that if you want it to scale well then I wouldn't go with MySQL. But I think your best bet is to test with several DB's and if whatever ones you choose work than that is what you support.

The answer to your last question is probably foreign keys. Do you know much about DB design? If not now would be a good time to learn before you progress to far and realize you have problems.
Daniel Prene
Ranch Hand

Joined: Jul 15, 2005
Posts: 241
MySQL leaks memory very badly, especially on updates.

What version of MySQL were you running?
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Originally posted by Daniel Prene:

What version of MySQL were you running?

4ish I think. I will have to check. Relatively recent and supposedly stable anyway.

You won't see these issues with small tables (at least I haven't) but repeatedly updating large tables causes steady leaks with memory usage for the server. I know it is the table that is the problem because IF I catch it I can flush the problem table and most of the memory is reclaimed. If I don't catch it then it just dies (the MySQL server) and restarts. Even when I do though like I said it only sort of reclaims the memory.. after three or four flushes it's pretty hopeless and has to be restarted anyway.

And I have tried many other things as well... even going so far as to totally recreate the table in the hope the problem was some sort of corruption (though I have no other errors) but nothing helps. I have even dropped all the indexes with the exception of the primary key. Nothing helps.

And in terms of size this DB is puny compared to my largest one, which is in SQL Server and has one table with 4.5 million rows and a couple of others with more than half a million.
Daniel Prene
Ranch Hand

Joined: Jul 15, 2005
Posts: 241
Thank you for your help, I really apreciate it. Since my server is running Debian Linux MSSQL isn't an option, but perhaps I'll seperate the servers... Would it be faster to seperate the database server from the rest? I'm low on funds, but have bunches of computers kicking arround... which do you think will require more resources, tomcat or the database server? Right now I have everything on the same server, but I plan to either start a linux cluster or break it up... what are your thoughts on that?

Thanks again,
-D.P.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61457
    
  67

Personally I'd avoid MS SQL Server like the plague. I had nothing but problems with it. My personal choice is PostgreSQL.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Daniel Prene
Ranch Hand

Joined: Jul 15, 2005
Posts: 241
My personal choice is PostgreSQL.

You have helped me out several times in the past and I value your desitions highly. I'll install PostreSQL tonight. . Once again, thanks for the help!

Any thought on my proposed database structure?
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Originally posted by Bear Bibeault:
Personally I'd avoid MS SQL Server like the plague. I had nothing but problems with it. My personal choice is PostgreSQL.


I am curious as to what kinds of problems you had with SQL Server. I am not a big one for MS in general but my working experience with SQL server has been surprising pleasant. So I am left considering that you have been doing things that I haven't done and I am wondering what they would be so I could keep that in mind for future reference.

Also to follow up from before the version of MySQL that has the leak is 4.1.15 on Linux. I haven't tested with 5 yet but the version of 4 I am using is the most recent stable version of 4 and to have this kind of problem I find disturbing.
Daniel Prene
Ranch Hand

Joined: Jul 15, 2005
Posts: 241
In irc.freenode.net/#mysql I was told this:
"an older version of the java mysql connector had a memory leak.. The most recent one is fine. It's highly unlikely that the current server has any sort of memory leak, but if you're concerned, search bugs.mysql.com for outstanding issues."

any thoughts? I looked around bugs.mysql.com and didn't find anything relevent...
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61457
    
  67

I am curious as to what kinds of problems you had with SQL Server.


Random crashing and network wierdness (now you see it, now you don't). To tell you the truth, I never figured out if it was MS SQL, XP or the combo. But I had no patience with trying to diagnose it when a better alternative (PostgreSQL on OS X) was available and worked right off the bat.
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Originally posted by Daniel Prene:
In irc.freenode.net/#mysql I was told this:
"an older version of the java mysql connector had a memory leak.. The most recent one is fine. It's highly unlikely that the current server has any sort of memory leak, but if you're concerned, search bugs.mysql.com for outstanding issues."

any thoughts? I looked around bugs.mysql.com and didn't find anything relevent...


My case is with PHP so I don't see that as relevant. I have searched the bug database myself with little results. I don't know what to tell you. This is a bug and it is MySQL. If flushing the table sortof rectifies the problem I think one can conclude once other controls have been established that the problem is indeed the server.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Architecture for online community