1 Topic by paul (edited by paul 08/21/2019 06:10:27 pm)

  • paul
  • Senior Member
  • Offline
  • From: USA, Columbia
  • Registered: 07/22/2019
  • Posts: 106

Topic: Database Prefix vs No Database Prefix?

Is there any benefit to using a database prefix when we are not presently limited to one (or very few) mysql databases on our system?

  One example I thought of: I didn't know if there could be easier store interactions from one company to a second company if they both share the same database with each having unique table prefixes as opposed to two separate databases - One owner, two semi-separate location situation: Locations can transfer material, but financials need to be kept separate.

  Also - With our initial company setup being tested in FA, this company database now has a prefix. If I wanted to use this data and not have a prefix, can the production setup be as easy as creating a new company in FA while specifying no prefix and a new database, use phpmyadmin to drop all of these tables, restore the test database (with prefix) to this new company and use phpmyadmin to replace the prefix with no prefix. Could this method break FA since the initial database had a prefix?

  Thank you,

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Database Prefix vs No Database Prefix?

Restoring from a wrong company's backup will limit / avoid data loss if separate prefixes are used.
Also, you can use multiple databases - one for each company - if there is no limitation on your hosting plan.
If the same mysql server hosts both databases, then getting consolidated reports from both database tables is possible and the table prefixes can help avoid using database name prefixes in the field names.

apmuthu's Website

  • paul
  • Senior Member
  • Offline
  • From: USA, Columbia
  • Registered: 07/22/2019
  • Posts: 106

Re: Database Prefix vs No Database Prefix?

Thank you apmuthu. If I understand you correctly, it seems that using a unique prefix for a company can be safer for data integrity, generally speaking.

Thank you

  • From: USA
  • Registered: 03/05/2018
  • Posts: 123

Re: Database Prefix vs No Database Prefix?

I agree that having prefixes does protect you from overwriting your company data with a different company's backup. However, if you are writing general report queries that you run outside FA, it would be simpler to have all the table names the same and just connect to the correct database, based on the company being queried.

I know, from a developer perspective, constantly entering queries at the command line and having to be sure to add the correct prefix to table names can be painful, but I have adapted to it. The real problem, as I stated earlier, is with using a generic reporting tool. AFAIK, there are no tools that provide macro replacement support to adjust table names before executing a query.

oakstreet1's Website

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Database Prefix vs No Database Prefix?

User variables can be used to some extent in MySQL but it does not work for table names.

apmuthu's Website