merging different databases

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
bobo12
Forum Newbie
Posts: 18
Joined: Sun Mar 04, 2007 3:48 pm

merging different databases

Post 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.
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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).
User avatar
veridicus
Forum Commoner
Posts: 86
Joined: Fri Feb 23, 2007 9:16 am

Post 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?
bobo12
Forum Newbie
Posts: 18
Joined: Sun Mar 04, 2007 3:48 pm

Post 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.
bobo12
Forum Newbie
Posts: 18
Joined: Sun Mar 04, 2007 3:48 pm

Post 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.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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.
bobo12
Forum Newbie
Posts: 18
Joined: Sun Mar 04, 2007 3:48 pm

Post 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.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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.
bobo12
Forum Newbie
Posts: 18
Joined: Sun Mar 04, 2007 3:48 pm

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
bobo12
Forum Newbie
Posts: 18
Joined: Sun Mar 04, 2007 3:48 pm

Post 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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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.
bobo12
Forum Newbie
Posts: 18
Joined: Sun Mar 04, 2007 3:48 pm

Post 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?
Post Reply