Jump to content

Forums by SQL


fred asselyon

Recommended Posts

Can I make a simple forum (no avatar and such, just making new topic and posting replies) application based on MySQL and PHP?This is my idea of the databases, i'm very green in databasing, i've learned some from Microsoft Access's lesson from school though:database one contains the topic name, topic ID, topic starter, number of replies, and date/time:

---------------------------------------------------------field name    |   primary key   |   type               |---------------------------------------------------------topic_ID      |   yes           | auto_increment |---------------------------------------------------------topic_name    |  no             | string               |---------------------------------------------------------topic_starter |  no             | string               |---------------------------------------------------------sum_replies   |  no             |  tinyint             |---------------------------------------------------------date/time     |  no             | date                |---------------------------------------------------------

Then i have database two that contains the topic ID (to relates with database one), replies, name of replier, reply no., date and time of the replies:

---------------------------------------------------------field name       |  primary key     |   type           |---------------------------------------------------------topic_ID         |  yes                  | tinyint          |---------------------------------------------------------replies          |  no                   | string           |---------------------------------------------------------reply_by         |  no                   | string           |---------------------------------------------------------reply_no         |  no                   | int               |---------------------------------------------------------date_time        | no                    |  date           |---------------------------------------------------------

I thought i'd just make a relationship between database one and two with the topic_ID. I know it's lame and slow, but I don't know how else to make a relationship between them.All i know is that each topic must has a table that contains the replies/posts data, but can you put a table inside a table?

Link to comment
Share on other sites

Well, for starters in database 2, the topic_id should be your foreign key, not primary key, since there will hopefully :) be multiple postings for each topic_id in database 2.Also, these should be tables, not databases. I would also suggest creating a third table for user information. It would contain: userid (primary key), name, date created, number of posts, and any additional info you would like to keep. Then in table 1, topic_starter would be a foreign key for user_id, and in table 2, reply_by would be a foreign key as well. That way you could keep more information on each user, and your tables would also not have to contain the username (string) for each post, but rather a user_id (int) which is smaller.Why in table 2 do you have a reply_no, if it is for sorting purposes, then you can always just sort based on the date_time.As far as the PHP side of things, I am not the person to ask about that, but the table setup seems like it could work as is, but continue to think on what else you might want to add to it in the future, and how that woudl fit in with your structure right now (such as user info).By the way, I actually think your initial design is done quite well, it is simple yet will still join the tables in an appropriate manner. I offer all the suggestions above only to try and help and also because you asked :) Hope all of this helps.

Link to comment
Share on other sites

I would suggest this for tables

|------------------------------------|TOPIC|------------------------------------|TopicId	|Yes	|auto number|TopicTitle	|no	|varchar|TopicStarter	|no	|varchar|TopicText	|no	|text|DateAdded	|no	|DateTime|------------------------------------|------------------------------------|REPLY|------------------------------------|ReplyId	|Yes	|auto number|TopicId	|no	|number|ReplyAuthor	|no	|varchar|ReplyText	|no	|text|DateAdded	|no	|DateTime|------------------------------------|------------------------------------|USER|------------------------------------|UserId  |Yes	|auto number|UserName	|no	|varchar|RealName	|no	|varchar|Password	|no	|text|Email  |no	|DateTime|------------------------------------

you don't need a field for the sum of replies to a topic...you would calculate that with PHP...if you try and manage that with the database it will evenually get messed up. It is better to use PHP to count the replies per topic.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...