Use 1 or more Databases

Posted on 2003-03-25
Medium Priority
Last Modified: 2013-12-24
I am in the process of creating a large website.  Should I have a different database for each of my systems?  For example, I have 400 clients now, would like this number to increase, and each of them will have access to atleast one of my systems.  And within that system for that client they might have up to 10 users(maybe even more).  I currently have 5 different systems that I am working on incorporating into my website.  I currently have 1 database with my account and user and system information.  I now want to start working on one of these systems.  I have many different functions that my user will be able to perform and therefore this will get quite large.  Should I create a new database for all of this information?  I am currently using Microsoft Access, should I switch to SQL Server?
Question by:swartout
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2

Expert Comment

ID: 8204716
First, the correct answer to "should I switch to SQL Server?" is a resounding YES.

As far as having multiple databases... this is really up to you. If your systems have any link to each other, then you might consider using the same DB for each, but if they are really seperate applications...I'd suggest leaving them seperate. There's no reason to mix this data if there is no relation.

Something you might consider is to have a seperate DB setup just for user accounts. This way, the user accounts are centralized rather then a user table in each DB. You may also consider putting some security tables in this 'user' database that will control each users access to each system you have setup.

To get a real answer, we'd need to know more about your systems and their relationships to the users and each other.

Author Comment

ID: 8204785
The systems do have links to each other, but are truely seperate applications.

I have one database set up now, that contains my login information, which consists of accountnumbers, usernames and passwords.  Also in this database I have the information regarding what systems these users have access to.  If they have access to the system, then I was going to call up the new database to take over from there, which would need to have accountnumber in it to let the user only have access to their account information.  

The main link between my two main systems is descriptions of levels and their values.  If they have purchased both of these, then we would want those identifiers to be consistant in both applications.

I do not know anything about SQL Server, what would I need to do to get started using it?  Is it SQL Server or MysQL?  I have heard of both of these.


Accepted Solution

TallerMike earned 200 total points
ID: 8205283
Having all of your systems in one DB does have some advantages:

- If these databases have similar tables, then you would end up with duplicate structure between the databases. If they were in the same database, then you could easily make a universal change to this table, rather than have to change it in every DB.

- If there is data in the seperate systems that relates to each other, you can join to it quickly and easily if it is in the same system. However, if you need to join data across 2 databases, you'll need to do either use query-a-query, or some other method to join them.

- Backing up your data is simple with a single database, just backup the whole thing.

Some disadvantages to having a single database:

- Since you'll be having all of the data for the systems stored in the same DB, you'll have to be extra careful that the user cannot access information from another system somehow.

- If something goes wrong with one of your databases, or you need to make a change to one of them that requires stopping the DB, then you'll need to take each system down.

- If at some point you get a large number of systems, you could move each database to a seperate server whichis not really an option if everyone uses the same DB.

- Keeping the data for each system is not going to be hard to keep completely clear when looking at the data in the DB.


If you're using SQL Server, you can create user accounts in the DB and create seperate tables in the DB associated with that account. Basically a table has an owner, and by default this is 'dbo' which is why you may see 'dbo.tableName' sometime. By making tables that are owned by a specific account, you can actually create multiple tables with the same name, all owned by different account:


And when the user logs in that is associated with that account, they can only see the tables that they own.

I can't really help you set this up though, cause I don't know enough about it really, but it's an idea!


SQL Server and mySQL are seperate DB servers. SQL Server is a Microsoft product, and mySQL is an open source database. I believe it's free. I would not want to get into a discussion about who is better... but if you're short on money, mySQL might be the best bet. Although I doubt it has quite the interface and 'bells and whistles' that SQL Server will give you. I don't know anything about comparisons in speed, reliability, nor robustness. You might consider looking them up before deciding. There is a DB forum here, you might consider posting a question there where more qualified DBA's could answer your question.

Author Comment

ID: 8205462
Thanks again for all of your assistance.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses
Course of the Month11 days, 11 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question