3

This is my school project. I am in chrage of eForum portion. I tried to delete a user and all the threads and replies by the deleted user will also be deleted.

    public boolean deleteUser() {
    boolean success = false;
    DBController db = new DBController();
    db.setUp("IT Innovation Project");
    String sql = "DELETE FROM forumUsers where users_id = " + userID + "";
    if (db.updateRequest(sql) == 1)
        success = true;
    db.terminate();
    return success;
}

This method retrieve the value of the first column of table and stored it as userID. Then, it will execute the delete sql statement to delete certain user.

    public boolean deleteThread() {
    boolean success = false;
    DBController db = new DBController();
    db.setUp("IT Innovation Project");
    String sql = "DELETE FROM forumTopics where topic_id = " + threadID
            + "";
    if (db.updateRequest(sql) == 1)
        success = true;
    db.terminate();
    return success;
}

As for this method, it will take the value of first column of another table which is showing all the threads and execute the delete sql statement.

However, after I deleted some user, the threads and replies by the deleted user is still storing in the database. I am currently using one user table and one thread table in database. Is there any way to like so called 'synchronize' both table? Is it the inner join ?

Thanks in advance.

Rauryn
  • 177
  • 2
  • 4
  • 16

1 Answers1

0

This seems to be a perfect case for setting up your relationships using Cascade Delete ( http://support.microsoft.com/kb/304466 )

Cascading Updates and Deletes

For relationships in which referential integrity is enforced, you can specify whether you want Microsoft Access to automatically cascade update or cascade delete related records. If you set these options, delete and update operations that would normally be prevented by referential integrity rules are allowed. When you delete records or change primary key values in a primary table, Microsoft Access makes the necessary changes to related tables to preserve referential integrity.

Note however, that Cascade Delete should be used with great care. These post refer to SQL Server, but the ideas apply to any database:

For the most part, I do not delete records first-off, I time-stamp them deleted for deletion at a later date, it is remarkable how often people change their minds.

cascade delete

Community
  • 1
  • 1
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • So what am I supposed to do? I am kind of confusing now – Rauryn Jan 31 '13 at 13:05
  • What do you want to do? It is your project, so you must decide if the user should be marked deleted for so many days and only records for users not marked deleted will be included, or if you want to copy records to an archive and then delete, or if your laws say that once a user says delete, it is your obligation to delete all records. – Fionnuala Jan 31 '13 at 13:11
  • So I should just simply put ON DELETE CASCADE at the sql statement which create users, threads and replies? – Rauryn Jan 31 '13 at 13:12
  • Sorry my mistake. I forgot to mention that this is an admin site. Only admin is available to perform deletion. – Rauryn Jan 31 '13 at 13:13
  • You mention MS Access, which means you have an interface for designing tables, relationships and queries. I would use it to get the database structured properly and then write code. – Fionnuala Jan 31 '13 at 13:15
  • Are you using MS Access or are you just using the free Jet/ACE download? Can you open MS Access in your computer? – Fionnuala Jan 31 '13 at 13:31
  • I am using MS Access and eclipse for my project. So am I supposed to use the inner join table thingy? – Rauryn Jan 31 '13 at 13:33
  • If you use the MS Access interface, you will save a lot of design time and you will be sure that your tables, relationships and queries are right for MS Access. You will be able to create and save queries and then call them with parameters through code. This will make your application much safer. There is a big difference between MS Access on your desk and MS Access on the internet. The usual thing is that you should not be using MS Access for a back-end database in this way on the net, but you said school project, so that is fine. – Fionnuala Jan 31 '13 at 13:41
  • The latest way of using Access in the internet is through Sharepoint, where it gets more powerful. – Fionnuala Jan 31 '13 at 13:42
  • It said relationship must be the same data type which means I cannot link the auto number to text? – Rauryn Jan 31 '13 at 13:42
  • You seem to have some design issues. You have a user table with UserID, correct? Surely the Threads table also has UserID to show who the thread belongs to? – Fionnuala Jan 31 '13 at 13:49
  • Inside my user table, userID is autonumber and userName is user name. As for the threads table, threadID is autoumber and topicBy is user name. But there is an error when I tried to link the userName and topicBy. – Rauryn Jan 31 '13 at 13:54
  • You need to reconsider your table design, you should not be using username in your thread table, you should be using UserID, that is what it is for. Look at the schema for StackOverflow here http://data.stackexchange.com/stackoverflow/query/new – Fionnuala Jan 31 '13 at 13:58
  • Okay okay I'll try my best within the last two days before my project due. Thanks for the help – Rauryn Jan 31 '13 at 14:01
  • What can I say? It really is better to use a proper design, but if you think you can get away with it, and I strongly recommend that you don't, you can find out why userName and topicBy do not match. For example, have you used the anti-feature, look-up field, in the table design? – Fionnuala Jan 31 '13 at 14:14
  • Thanks I fixed it already. I using the select sql statement and where clause for example : Select forumReplies.reply_ID,forumReplies.reply_topic,forumTopics.topic_title,forumReplies.reply_content,forumReplies.reply_by from forumReplies,forumTopics,forumUsers WHERE forumReplies.reply_topic = forumTopics.topic_id AND forumUsers.users_userName = forumReplies.reply_by so when I delete user or thread, the three tables may synchronize – Rauryn Feb 01 '13 at 04:37