10 replies to this topic

[xiou]

Is it possible to use multiple databases for one website. What I have in mind is having one database that stores user information and administration information. Then I would have different databases depending on the user's information. The admins will have full privileges to everything on the site but the members could only use one database based on their email/state/sex/etc.

Is this possible to do? I kinda want it to be like facebook's site right when it came out in how only people from one college could look at people from the same college. If they tried to look at someone from a different college, they were denied.

If someone could point me in the right direction, I would thankful.

[Orjan]

ofcourse it is, as long as your serverprovider allows more than one db.

I do it in a system I run.

make a sort of a base db with a table with users, and you set a db name as a parameter on the user.

when user is verified, just do a changedb($usertable[database]) or similar in your programming language, then just carry on running your page. if you have identical tables on every database,

in MySQL, you can otherwise access different databases by addresing the table with punktuation: database.table.field

Here's an example for MySQL, i guess most SQL is alike.

select b.field7 from myfirstdatabase.table1 a, myseconddatabase.mytable2 b

where a.field1 = b.field2

[WingedPanther]

The answer will depend a little bit on the server-side language you use, but I expect it is always "yes". The details will depend on the language, of course. Everything you are describing could be done with a single database, however.

[xiou]

Thanks for the responds.

The language I plan on using is PHP. Do either of you know any tutorials that will set me on the path to understanding this problem? Thanks again for the responds.

[Xav]

Try PHP Tutorial.

[Orjan]

the most common way to solve the problem of user grouping and such is to set either a column with group number in the user table so you always query out the user's group. the other way is if user is to be in several groups, make another table with just groupnumber and userid (and prefferably an auto-incrementing id, and index on both groupid and userid separately) and first query which groups the user is member of, then query all members of all those groups, one at a time or all at one as wished.

[Sionofdarkness]

I believe most large websites have multiple databases for different kinds of information.

[Orjan]

not necessarily, as far as I know, you don't gain speed of dividing in databases unless you split large tables into several?
better would be to build a clustered server instead, with one great database.

things where you can gain with using several databases would be if you run several identical sites with no need to communicate between sites...
as a site of many forums put a new db for each forum to separate customers or similar.

thats my thaughts

[tecktalk]

yes you can.. please check with your hosting service provider if he has allowed you to do so.. is it in you package or not.

[Orjan]

I think it would be slower if you need to access data from all the tables at some points, and you need to union all tables from all databases that you need a total view of.

a pure select must be faster than making loads of unions.