Expert MySQL questions

[craig1]

I didn't know where to post this since it really isn't a Jag support issue.

Couple MySQL related questions:

1) As related to security and performance, is it 'better' to have multiple databases or one database with 100's of tables (assuming of course that there is absolutely no interaction between the DB's)?

2) As related to security and performance, is it 'better' to have multiple MySQL users accessing the DB(s) or one 'master' user?

[Galen]

Originally posted by craig1
I didn't know where to post this since it really isn't a Jag support issue.

Perhaps the "PHP & MySQL" forum would've worked? Just a hunch

[jhilgeman]

It's usually better to have a separate DB per major application, just for organization purposes.

In regards to security, it really depends on how many users are accessing the database directly and who wants access to what - it's impossible to give you an accurate answer unless we know the entire setup. When it comes to me, I almost never give direct access to the database to anyone - if I need people to be able to update the data, I create web page forms for them to do that. Much safer and controlled and they don't need to know any passwords.

However, if you do need others to have direct access to the database, then remember that MySQL has flexible user management. You can:
- create a user that just has read-only access to one particular table
- create a user that can manage tables 1, 3, and 8
- create a user that has full privileges to one database and not the other
- create another admin-type of user with privileges to do anything.
Those are just 4 possibilities to illustrate that you can set up almost any sort of security system for MySQL management. But again, it's usually far easier, simpler, and safer to just have web pages do the work for you (not to mention more portable - some web hosts don't allow you to handle user management)

Performance-wise, I don't think it really matters one way or another. If there's a difference, it's minimal. I've had both setups - multiple databases and also one database with hundreds of tables, and both ran smoothly.

- Jonathan

[dkadave]

I use one database with lots of tables. But I don't have hundreds. How do you get hundreds of tables?

Well I use the one database for my site. Which has a couple services. So to make the MySQL Connections to a min. I just combined everything to one database. And in the script is calls the serveral tables it needs.

Well for me I don't allow anyone to use my DB's. It's for my security.

And it allowed me to make an admin center where I can edit all my tables. Instead of making the script decide which database to open and then open the desired table. If you use phpmyadmin it would be more organized I guess if each was in different DB's. But if you are going to make a script your self to manage the tables then I would put them in one DB.