Many tables or many databases ???

[southbeach]

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!

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.

 

[vacunita]

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

 

[feherke]

Hi

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

 

[southbeach]

@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.

 

[vacunita]

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

 

[southbeach]

@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.