Was thinking of just dumping users to an "inactive" state as well upon
deleting... it would solve my problem with the join issue for sure ... would
also create alot of database clutter for sure ... Unused user accounts and
such ... I don't like having data in the database that serves no real
purpose ...
The problem with this is legal issues. According to Danish law, you're not
permitted to save a person's details for more than a certain amount of time
after their account has been cancelled/discontinued. The amount of time is 6
months to 2 years, depending on what kinda details we're talking about (I
haven't checked what would apply to my situation, but it goes for any kind
of business, service, or organization that uses user registration and
storage of personal details)...
Since I can't easily check what the law on this is in all 193 countries
around the world, I simply want to avoid the risk of running into this, or
for anyone that uses my forums running into it (eventually when I get all
the functionality written, it will be released on some form of open source
license).
That's why I want to have a way to just remove the accounts from the system,
without breaking it...
Short of a few notes, I haven't even started on designing the ban system
yet, but I know it will take more than 1 flag for the system to uphold the
bans to the limit I want it to (temporary bans, ban history, and "banned
reason" response system is among the things I'm looking at doing).
Rene
According to historical records, on Wed, 21 Apr 2004 16:18:45 +0400 Mikhail
U. Petrov wrote about "Re: Handling missing rows on joins":
>Hi!
>I think it'd be good to add in hf_users column status - ENUM('Active',
>'Blocked', 'Deleted'), and when user
>will be deleted you should set this status to Deleted. Value 'Blocked'
>may give you a simple way to ban user :)
>Yes, this method of deletion take more database space, but it'd be
>better, I think...
>
>Wednesday, April 21, 2004, 1:22:30 PM, -{ wrote:
>
>RB> Working with PHP 4.3.0 and MySQL 4.0.14 (but code MUST be compatible with
>RB> 3.23.56).
>
>RB> For my forums, I've got this 4-way join to load (and output) the posts with
>RB> info on the poster:
>
>RB> $fullthread = "SELECT
>RB> postID,hf_posts.userID,name,time,post,edit,nickname,joined,location,posts,levelname
>RB> FROM hf_posts,hf_users,hf_user_stats,hf_levels
>RB> WHERE hf_posts.threadID='$threadID' AND
>RB> hf_posts.userID=hf_users.userID AND hf_user_stats.userID=hf_users.userID AND
>RB> hf_levels.levelID=hf_users.levelID
>RB> ORDER BY hf_posts.time ASC";
>
>RB> I'm not gonna paste the entire table structure, cuz it comes to rather many
>RB> rows those 4 tables make up. But it's pretty basic really: hf_posts contain
>RB> the posts in the forum. hf_users contains the static userinfo = the user's
>RB> profile, level assignment and a few control values. hf_user_stats contains
>RB> the dynamic user info - number of posts & threads, last login and logout,
>RB> last IP, and things like that. hf_levels contains information and general
>RB> perms for the user levels.
>
>RB> hf_posts has postID as primary key, and threadID and userID as foreign keys.
>RB> hf_users has userID as primary key, and levelID as foreign key.
>RB> hf_user_stats is basically an extension of hf_users, and thus only have
>RB> userID as primary key, but it also functions as foreign key.
>RB> hf_levels has levelID as primary key, and no foreign keys (it's a top-level
>RB> table in the relations).
>
>RB> The way it's made, when a post is made, the poster's userID and displayname
>RB> is stored in hf_posts under 'userID' and 'name' respectively.
>RB> But the poster's current displayname is always in hf_users.nickname. Posts
>RB> by unregistered users (or those not logged in) are stored with userID 1.
>
>RB> Since the system allows unregistered users to post, I've got a guest user
>RB> "implanted" into the hf_users table on ID 1, just so the join won't fail to
>RB> display those posts. When the script outputs the posts, it checks the userID
>RB> of the posts. If the userID = 1, then it prints the displayname stored with
>RB> the post, and skips the userdata for that post, otherwise it uses the
>RB> displayname from the users table.
>
>RB> But here comes the problem: If a user is deleted, the records from hf_users
>RB> and hf_user_stats will be gone. On the join, this means that any posts made
>RB> by the deleted users will not be included, and thus not displayed -- they
>RB> become dangling posts in the database ...
>
>RB> I haven't actually written the code to delete users yet, but my current idea
>RB> for a workaround is that on deleting the users, changing all their posts to
>RB> userID 1 (the system guest level). This would atleast let the posts be
>RB> displayed, although make those posts display as posted by a guest, and
>RB> because the displayname is stored with the post, it would not list as
>RB> Visitor/guest.
>
>RB> What I have problem with is figuring out whether this is a kludge or the
>RB> only way to do it. Or is there a better way to make the join so it will
>RB> include the posts even if it cannot find anything to join with in hf_users
>RB> and hf_user_stats ???
>
>RB> I know it looks like deleting levels will cause problems as well, but the
>RB> code for that has been made so deleting a level will move all users assigned
>RB> to that level to a different one.
>
>RB> Any suggestions for handling this better would be appreciated ...
--
Rene Brehmer
aka Metalbunny
~ If you don't like what I have to say ... don't read it ~
http://metalbunny.net/
References, tools, and other useful stuff...
Check out the new Metalbunny forums @ http://forums.metalbunny.net/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
