Asp

[artsapimp]

I am trying to make something which can best be described as a bulletin board a lot like the UBB we're working on right now. Everything is going very smoothly except the adding of new records. It's possible my database structure is wrong but when I'm trying to add a new thread or reply I have open 3 or 4 tables on one page just to create the new post.

Does this sound right to anyone else? Does anyone know what the DB structure should look like? Below I have drawn my DB structure. Please correct anything you see that is wrong.

Category:
-CategoryID (autonumber
-Category (text)

Topic:
-TopicID (autonumber)
-Topic (text)
-Summary (memo)
-Category (number)
-LastPostedDate (date/time)
-LastPostedTime (date/time)
-LastPostedBy (number)

Thread:
-ThreadID (autonumber)
-Thread (number)
-Subject (text)
-Message (memo)
-StartedBy (number)
-LastPostedDate (date/time)
-LastPostedTime (date/time)
-LastPostedBy (date/time)
-Topic (number)

Message:
-MessageID (autonumber)
-User (number)
-Date (date/time)
-Time (date/time)

User:
-UserID (autonumber)
-Username (text)
-Password (text)
-email (memo)

With this structure pulling everything out is very easy. Putting stuff in is more of a pain than I've ever imagined.

Thanks for any help.

Art Sapimp

[mosquito]

I would remove the -Message (memo) field from the thread table, and use the first message in a thread for this. You also have to add user information to your thread table.

I would use this model

category(category_id, category, owner, audit_date, audit_user)
topic(topic_id, topic, title, category, owner, audit_date, audit_user)
thread(tid, subject, owner, status, creationdate, lastpostdate, audit_date, audit_user)
message(mid, text, owner, status, post_date, audit_date, audit_user)
map_thread_message(tid, mid, audit_date, audit_user)


==> Security tables
user(uid, uname, upass, postcount, last_login, quote, email, status, audit_date, audit_user)
role(rid, rdescription, audit_date, audit_user)
permissions(pid, pdescription)
map_role_permission(pid, rid, audit_user, audit_date)

audit_user and audit_date is simply used to monitor which process is writing the information (a little extra protection)

I also specified some mapping tables to make it easy to move messages.

role and permission are specified to make it easier to administrate the forum.

The status fields are used to specify if a message or thread is locked, deleted,... I would never delete a message from the database, but I would only set the


Do note that this is a very normalised model. For performance reasons I think you'll have to denormalize it.

[artsapimp]

The message (memo) in thread was a typo. It is in the message table.

I have used my thread table which is mostly numbers to do the mapping. I am pretty confident it is doing everything correctly right now, but I will try your suggested tables and see how it goes.

As of right now I'm not going to apply a role or permission. I have a yes/no box in User which defines if the user is an admin or not. That is the extent of what I'll be doing with that for now.

Can you explain the audit_user and audit_date a little more please? I am not sure I understand exactly what they are for.

You said for performance reasons I would have to denormalize it. What exactly would you be referring to there? The work it requires from my server seems very small and it's running great so far. I know once I throw 35 simultaneous users on it the server might not keep up so well, but my hands are kind of tied by the applicaiton on how I can resolve that right?

[mosquito]

The audit_user and audit_date is something I use in every table to monitor who changes what in the record. The main reason is to be able to tell if there have been manual changes to the tables. I usually fill those fields with a trigger (in sql server it's just inserting system_user and current_timestamp)

If you are facing big loads on your server you might want to add username to the message table and some other stuff. This to make your joins less complex, and thus lowering the load on your server (the cost is of course more disk space)

[artsapimp]

I'm not too worried about the disk space. I will be working on that side of it once I have a 100% working application.

Because this is the first time I'm creating anything like this and I'm using many different sources with different ideas to make it work this is kind of a sloppy copy. I am sure this will work great but it's still the alpha version.

Speaking of SQL server, do you know of any good tutorials or books to quickly highlight the necessary steps to creating my first SQL database? I have books from Microsoft but they are less interesting to read than anything I've even seen. I can't make it past the first 10 pages without falling asleep.

Thanks.

[mosquito]

I like the books from wrox http://www.wrox.com/Books/books.asp?...ect=SQL+Server

If you make your databases in SQL Server, make sure that you write scripts, and don't mess in enterprise manager. If you make separate scripts, you can rebuild your database at any time.

If you decide to do this, I can send you a batch file that generates a complete database from the scripts.

[artsapimp]

Thank you. That would be very helpful if you could send me that.

artsapimp@yahoo.com

I have a developer version of 2000 installed right now but it just sits there because I have no idea what any of the applications mean. I don't really know what you mean by the scripts as opposed to the enterprise manager but I'm sure that will be explained better in the book.

Thanks again for your help.