Page 1 of 1

merging different databases

Posted: Tue Mar 06, 2007 7:42 am
by bobo12
I have two services on my website and both use different databases. Users are forced to signup twice because of this, and I would like that to change. What I'm wondering is the what I'm up against merging the databases or just the usertables (bridging them?). Tutorials, comments, suggestions and smart remarks are welcome.

Posted: Tue Mar 06, 2007 8:04 am
by Begby
This could be easy or hard, but more detail is needed.

Are the databases the same type? Like mysql or whatever. Are they on the same server?

What are the table structures?

Are we talking about like pre-written forum or vendor software where you want to have users sign up to your site and have access the the other app that you didn't code? (maybe not exactly like that, but you know what I mean).

Posted: Tue Mar 06, 2007 11:13 am
by veridicus
As Begby says, it could be easy or hard depending on the situation. Can both systems share something generic in common, like OpenID?

Posted: Tue Mar 06, 2007 2:13 pm
by bobo12
Begby wrote:This could be easy or hard, but more detail is needed.

Are the databases the same type? Like mysql or whatever.
Yes, both databases are mysql and both are on the same server.

Are we talking about like pre-written forum or vendor software where you want to have users sign up to your site and have access the the other app that you didn't code? (maybe not exactly like that, but you know what I mean).
Yes, both services are pre-written. One is a vbulletin forum and the other is a script repository. When a user signs up at x they should automatically have access to y and vise-versa. Or just have a single signup location?

What are the table structures?
Are you talking about just the user table or all tables? my vbulletin has 120 tables and I'm not quite sure how many the other has. I don't have the script repository yet, but should within 3 days.
Can both systems share something generic in common, like OpenID?
Both will require users, so yeah, I guess they could share a userID.


Part of vb3_user table structure:

userid (primary key)
usergroupid
displaygroudid
username
password

There's probably 20-25 tables in vb3_user, so I won't post them all unless you ask me too. As I mentioned before, I should have the other service in a few days. Will it be easier to merge these things if I hold off on installing the new service (script repository)?

Thanks guys. Please do tell if I'm not supplying enough information.

Posted: Fri Mar 09, 2007 5:35 pm
by bobo12
Alright, I have the other service. Please let me know what kind of information you need, because I really need this :(

The script service's member table looks like this:

sbuser_id
sbuser_name
sbuser_pwd
sbemail_addr
sbfirst_name
sbhome_page
sb_location
sbtemp_date
sbsignup_date
sbapproved
sbverified
sbverification_code

and the forums member table looks something like this (with a lot more rows):

userid (primary key)
usergroupid
displaygroudid
username
password



Thanks for any help or advice.

Posted: Sat Mar 10, 2007 4:16 am
by mikeq
Noone is going to write this for you, we'll give you pointers but you have to have a go at it.

Basically as someone creates a new record on one of your user tables (inserts) then you are going to have to pick up this data and populate the other user table.

Posted: Sat Mar 10, 2007 7:07 am
by bobo12
mikeq wrote:Noone is going to write this for you, we'll give you pointers but you have to have a go at it.
Hey wise guy, if you read over my posts I'm looking for advise and pointers, not for someone to do this for me.

Basically as someone creates a new record on one of your user tables (inserts) then you are going to have to pick up this data and populate the other user table.
I could have guessed that, but the problem is making it happen. I don't know how (and haven't seen anyone tell how) to populate the other table.

Posted: Sat Mar 10, 2007 11:53 am
by mikeq
okay,

In your script service application you will have a form that the user fills in to create a new user.

When that form posts it will be inserting a record into your sbuser table, just after it does a successful insert into the sbuser table you need to pick up the same values posted and do some php to insert into forum user table.

If you are using MySQL 5 then you can probably do something with triggers.

Posted: Sat Mar 10, 2007 12:10 pm
by bobo12
I see, so the best way of doing this isn't actually merging the tables into one, but making a 'bridge'. I think I could manage to do that.

That way I won't have to worry about incompatibilities between the tables. Hopefully the vbulletin database won't be too big of a pain to deal with.


Thanks.

Posted: Sat Mar 10, 2007 12:32 pm
by John Cartwright
When someone signs up to the script repository, why not just crease a new user in the vbulletin user table (you might want to dig up the vBulletin code to check how they register the user), and then when they go to log in on the script repository have it read the user table in vBulletin. Or even just redirect them to sign up to your forums, and explain that they will have access to the forums and the script repository.

This has several advantages over the recommended suggestions,

1) You'll only have to maintain a single user table, instead of having to worry about keeping both user tables in synch
2) Your already existing vBulletin users will automatically have access to your script repository


One disadvantage I can think of is you'll have to recreate user accounts in vBulletin for the existing script repository table.

Posted: Sat Mar 10, 2007 6:24 pm
by bobo12
Jcart wrote:When someone signs up to the script repository, why not just crease a new user in the vbulletin user table (you might want to dig up the vBulletin code to check how they register the user), and then when they go to log in on the script repository have it read the user table in vBulletin. Or even just redirect them to sign up to your forums, and explain that they will have access to the forums and the script repository.

This has several advantages over the recommended suggestions,

1) You'll only have to maintain a single user table, instead of having to worry about keeping both user tables in synch
2) Your already existing vBulletin users will automatically have access to your script repository


One disadvantage I can think of is you'll have to recreate user accounts in vBulletin for the existing script repository table.
I like this approach even better. The good thing is I don't have any users in the script repository as it's something I just added.

Now, there are some extra rows in the script repository, so could I run an sql query on the user table in vbulletin and add those without any problems?

Posted: Sat Mar 10, 2007 6:47 pm
by John Cartwright
bobo12 wrote:Now, there are some extra rows in the script repository, so could I run an sql query on the user table in vbulletin and add those without any problems?
Right.

Posted: Sun Mar 11, 2007 8:08 pm
by Begby
One thing to keep in mind, if at all possible modify as little code as possible in vbulletin and the other software. Ideally none at all. Maybe you can make a user signup page that just writes to the tables of both databases.

If you start modifying the code of the two prewritten packages you run the risk of breaking future upgrades and patches. And widely used open source bulletin board software is definitely something you want to have patched.

Posted: Sun Mar 18, 2007 5:39 pm
by bobo12
thanks guys. I have the signup page writing to both databases, but what are the consequences when/if the site gets very large? For instance, if I have 2 million users, then I automatically have 4 million entries (since I'm inserting twice). Will there be a huge overhead or lag?