Is it a bad idea to connect two databases like this? How can it be done?

Question

I am currently using Simple Machines Forum on my website, and users both register AND login using their forum account on my website. The smf_members table contains fields such as:

id_member | member_name | date_registered

What I am trying to do now is extend this to add more custom fields and connectivity on my site. I want to use the id_member field for many things:

For example, I want a user (an entry in this smf_members table) to be able to join a team.

I am going to create a table Teams with the following fields:

ID | Name | Description

and a table TeamMembership with the following fields:

UserID | TeamID | Role

As you can see, this table will link a member and a team, while leaving data specific to a user or team in their respective tables ONLY (successfully preventing redundant data). This sounds good, right?

Well, I don't want those two new tables in the same database as the SMF stuff, because it may get messy. Is it the easiest solution, though? Do you think the easiest solution is to just create my new tables within the same database, with the prefix cst for Custom? I have no idea how to link two databases so if it is too complicated maybe I should just do my cst solution.

I've edited this post and have an additional question.

Thank you for the answers. I have an additional question. Let's say that I wanted to extend new variables to the members, but again, wanted to avoid adding new fields into the SMF forum member table. What is the easiest way about going about this? Like, I want to create a table called UsersExtended and have fields such as:

-ID (this is NOT an auto-increment field, but the value of id_member from the SMF members table)

-Country

Is it easy to create a profile page with this structure and display any relevant data I want from the two tables, in a way, Linking the two so that they act as one big table?

score 3 · Answer 1 · answered Jul 31 '11 at 19:13

3

They totally belong in the same database. Use one db and join the tables in your queries.

The only time you want to build a unique database for your application (other than for the application itself) is if you intend to create an API which will serve up that data to other projects in an unbiased manner. That is very common with Solr in which you can churn out blazing fast API's and generally don't belong mixed in with your current MySQL tables.

answered Jul 31 '11 at 19:13

AlienWebguy

76,997
17
122
145

Thank you for the help. I am posting an update to my initial question, and as I'm new to Stackoverflow.com, I'm not sure if my question gets bumped to the top of the page again when I edit my question. – pr0tocol Jul 31 '11 at 22:00

score 1 · Answer 2 · answered Jul 31 '11 at 19:14

1

There is absolutely no harm in having the custom tables in the same database. You cannot define relations between 2 different databases in a relational database system.

answered Jul 31 '11 at 19:14

shashi

4,616
9
50
77

Oh jeez I guess you are right lmao. Thank you so much for the help. Question, should I prefix with anything so they are easy to spot? – pr0tocol Jul 31 '11 at 19:18
well, that depends on your preference. If I were you I would not add any prefixes to the tables that I create. The tables created by default already have the prefix smf, so you know that those are the ones that were not created by you. I would just let my table be teams instead of cst_teams. That ways, the queries you write are more readable in my opinion. – shashi Jul 31 '11 at 19:25
1

@sassyboy: _You cannot define relations between 2 different databases in a relational database system_ - it depends on database system, MySQL allows it. – Vlad Aug 01 '11 at 07:19
Wow.. I was not aware of that! – shashi Aug 01 '11 at 09:09

score 1 · Answer 3 · edited May 23 '17 at 12:19

1

If you're using MySQL have a look at this question's accepted answer. Remember you have to prefix your table with database name when querying across databases.

edited May 23 '17 at 12:19

Community

1
1

answered Jul 31 '11 at 19:27

Vlad

10,602
2
36
38

score 0 · Answer 4 · edited Jul 31 '11 at 19:18

0

You may try this

SELECT * 
from database1.table, database2.table 
where database1.table.id=database2.table.id

edited Jul 31 '11 at 19:18

Adriano Carneiro

57,693
12
90
123

answered Jul 31 '11 at 19:14

NickCL

1

score 0 · Answer 5 · answered Jul 31 '11 at 19:21

You should only create two tables within the same database

**smf_members**

id_member | member_name | date_registered

**TeamMembership**

Team_ID | Name | Description | Role | id_member

If any user wants team membership the you can easily manipulate its record using join queries.

score 0 · Answer 6 · answered Jul 31 '11 at 19:37

Don't know Simple Machines Forum - but I do know that most applications expect to be the only show in town.

I would create a new database = perhaps called "teams" with your own tables, and use NickCL's way of doing cross-database joins to join between the two applications.

You don't mention the specific database engine you're using (I assume it's MySQL), but logically, you can think of databases as namespaces - a way of keeping stuff that belongs together in the same logical place.

Ideally, "people"/"authentication" etc. should be in a separate database from the forums stuff - but as you're working with off-the-shelf code, you don't have that luxury.

In my experience, it's better not to mess with the databases of off-the-shelf software - when you want to upgrade, you have no idea what will happen to your own tables, regardless of their names....

Is it a bad idea to connect two databases like this? How can it be done?

6 Answers6