×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Many tables or many databases ???

Many tables or many databases ???

Many tables or many databases ???

(OP)
OK, working on a project where, I hope, the user base can reach a reasonably high volume.

Each prospective user would have their own data and must be apart from one another (companies can do their billing, project management, etc.).

I have done similar work where every time a company is registered, a unique ID is assigned and this ID is used to mirror the "tables" in the database. This has worked fine and thus far I have not ran into problems.

Fair to say, that the number of unique companies have not really reached a number where one could say: boy, I'm banking it! bluegreedy

Now, I have always asked and debated with myself the question: Would it be better if I mirror the entire database?

Some of the challenges that have kept me using "many tables" in lieu of "many databases" is that often times, the need to a "global" table (one that is shared by all) is needed and I figure it would be best if only for this particular reason.

Time to ask those that likely have been down this path and can offer advise based on identical experience and their personal observation.

What is the best approach: to have many databases or to have many tables?

Thank you all in advance for your kind assistance.

--
SouthBeach
http://www.fp2php.com
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

RE: Many tables or many databases ???

I'd say it depends greatly on what exactly the "application" or "service" is geared towards, and its usage type.

If all companies are expected to use this system from a central location such as an online server for instance, I'd say you could probably handle each company's data in a single DB and single set of tables by having full control of company IDs, and sub item relations to that company ID.

i.e a Table of companies with relationships with other tables like users, items, projects, billing types etc.. with normalizing tables for relationships.

companies_to_users, users_to_projects, etc...

In very rare circumstances would I ever suggest having duplicate tables for each company in the same database. You either have entirely separate DBs, or manage everything in the same set of tables.

Your system should be able to tell if a project or bill belongs to a specific user, that user is from a specific company etc...

In other words, your control and data separation should be managed by your queries and item IDs. Adding duplicate tables simply points out the weakness in your DB design if its not able to separate company data correctly and uniquely so to speak.

Database mirroring or more accurately structure duplicating, since you won't be mirroring the data, would be useful if you plan to give each company access credentials to the actual database. ie. you would not want them to be accessing an large central database with other companies' data.

Also if you plan on servicing many companies, an aggressive backup and recovery plan should be in place. You do not want to lose a company's data and have no way to restore it at least close to what it was before the unfortunate event.

A Single database makes this easy to manage since you only need to mirror and backup a single DB. However this also means that if something goes wrong all companies are affected simultaneously.





----------------------------------
Phil AKA Vacunita
----------------------------------
OS-ception: Running Linux on a Virtual Machine in Windows which itself is running in a Virtual Machine on Mac OSx.

Web & Tech

RE: Many tables or many databases ???

Hi

Quote (Phil)

A Single database makes this easy to manage since you only need to mirror and backup a single DB. However this also means that if something goes wrong all companies are affected simultaneously.
The above part of Phil's answer is the one I would think at first. Is there any chance you will receive an e-mail one day saying "Dear mr SouthBeach, we accidentally banged all our records. Is our fault and we assume the possible losses, but please restore the database from the last available backup." ? If yes, then I would definitely go with separate databases.

Feherke.
feherke.ga

RE: Many tables or many databases ???

(OP)
@vacunita,

Your suggestion of doing the relation based on company ID is something I currently employ. My most recent project does exactly that but, combined with unique tables per company.

There are tables where the data is shared by the entire pool and so, these use company ID as means to know who owns the row (the Tariff in our case). Knowing who owns the Tariff is critical since that would be the party that gets paid should that tariff be used by a 3rd party ...

I have opted the unique tables solely in fear of "processing speed" ... I figure that if I create tables where each company keeps its own sales history, the speed of process would be much better since each is dealing with their own volume; where as the combined volume could cause process to take reasonably longer.

@feherke,
For the project I am currently working on I have really lean to separate databases. If I intend to let anyone register on line, let them try system for 90 days and if they stick, great, one more source of income; if not, purge the database.

__
As far as table structure integrity and continuity, I guess it is in my hands to make sure that when a column is added, edited or whatever is done, same is spread across all databases.

--
SouthBeach
http://www.fp2php.com
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

RE: Many tables or many databases ???

Quote:

have opted the unique tables solely in fear of "processing speed" ... I figure that if I create tables where each company keeps its own sales history, the speed of process would be much better since each is dealing with their own volume; where as the combined volume could cause process to take reasonably longer.

If your tables are correctly indexed, and your queries optimized, the speed you may gain from individual tables would be negligible. Yes you will have less records to query per table, but at the end of the day, unless your queries are based on likes or other extremely slow comparisons, your speed gain will not be very noticeable.

----------------------------------
Phil AKA Vacunita
----------------------------------
OS-ception: Running Linux on a Virtual Machine in Windows which itself is running in a Virtual Machine on Mac OSx.

Web & Tech

RE: Many tables or many databases ???

(OP)
@vacunita,

There are instances where I use LIKE to query - actually, it is used in a module one could consider to be the primary point of process of dashboard.

This page is where operators can monitor all pending jobs, track them by a number of key fields (some indexed, some not). Because operators not always have exact, or so they claim, I use LIKE a mean to filter/limit data shown - Since it is expected rows will reach 1000s, page shows 50/100/150/200/250/300 rows per page (whatever the operator chooses).

I also have in place a "capture all" queries mechanism which writes all queries made to a table. The idea is to review this table in hope to identify ways to improve processing time by indexing tables/columns based on query history or most used queries.

Hope all of this makes sense in writing as it does in my head! lol

It is a very young application and I must confess that not all indexes are in use given that I am trying to build enough data to better judge how to best optimize the tables (and I do define them best I can) - That said, I also know that I have much to learn to capitalize DB engine built in power that should make my work easier.

--
SouthBeach
http://www.fp2php.com
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close