April 25, 2005 at 3:57 pm
Awhile back we were required to seperate our forum's membership from our website's membership. Now, there's no requirement, and it's getting old having to sign in two seperate places. I'd like the membership and forum's to at least have the same membership table (Users) so people don't have to sign up/in two seperate places in the future. There are two different User tables: Users U and ForumUsers FU
Since the site has been around for awhile now Users has around 6000 records, and ForumUsers has 2000. Both have a primary key UserID. Now onto the problem...
Users and ForumUsers have UserID's that overlap. A user with the UserID of 12 in U is most likely not the same user as the user with the UserID of 12 in FU. Each User tables has around 12-20 related tables, meaning that the UserID is a foriegn key in it.
What I was thinking is that I could create a single new table to merge the records UsersNew UN. Then I'd just delete both the other tables when finished and change the programming to recognize the change. The problem being:
How do I make sure that the related table's foriegn keys are updated as well? For instance if I have a table called
User_Roles
--------------
PK User_RoleID
FK UserID
FK RoleID
and I merge the tables together and a user in U had a UserID of 12 beforehand and now has a UserID value of 6009 the UserID in User_Roles needs to change from 12 to 6009. Otherwise the roles that the user had beforehand won't be the same, so the merge would be useless.
I'll keep you posted on my progress. I'm just figuring, if someone has the answer, I'd sure be greatful if they might share! Thanks in advance.
April 25, 2005 at 4:09 pm
Sure enough, as I wrote this I came up with a plan. I've always selected Cascade Update/Delete when creating the relationships between tables. I went through SQL Analyser after turning off IDENTITY in U, and tried to update a single record in my Users table, and sure enough, it automatically updated my related tables.
Ex:
UPDATE Users
SET UserID = 9000
WHERE UserID = 1;
My FU table's last ID is 2080. I guess I could write a program to do something like:
Pardon my psuedo-code:
i=2081
foreach record...
{
UPDATE Users
SET UserID = x
x++
}
Then I could import that table right into my other table and I'd be done?!
April 25, 2005 at 5:03 pm
Getting further...
UPDATE Users
SET UserID = UserID + 2080;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply