SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    London
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Advantages of using different databases for one application!

    Hi,
    I have an application that uses a MS SQL database for core data values. I will be having a number of companies all with their users associated to them. However, the look of the website will be tailored per company so I have created a separate database per company to include these core values and style information. I thought this would make sense as to keep all company information in one database, whilst having the core application data on another.

    My question is whether this is advantageous to having the tables in one database and simply using a lot of (and I mean a lot of) fields to identify the company/user, e.g. CompanyId, USerId etc etc.

    Upon my server side pages, I am sometimes needing to connect to one database and then another, but always ensure only one connection is open at a time. So there may be connectivity issues as well.

    Let me know your thoughts...
    Regards

  2. #2
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's a fairly poor design decision, IMHO.
    Here is a selection of some of the problems you will have:

    You will place extra strain on the hardware, managing a database, the logs, transactions, backups, permissions etc. requires some server performance.

    As you noted, you will have to manage separate connections, or you will have to do everything through 3 part object names.

    You will have problems with referential integrity in your database design. AFAIK, you can't distribute FK / PK or referential constraints across databases.

    You will have problems as soon as you start wanting to use 3rd party tools, such as crystal, sisyphus, OLAP, mssearch, replication. None of this stuff was designed to cope with poor design decisions like splitting an application's data across several databases.

    If you really must do something like this, then MSSQL offers much more support for partitioning your data across separate database servers. You can do things like distributed partitioned views and federated servers, but I'm guessing that you don't want to do that.

    My advice is: one application one database. As far as I can tell, you are looking at building something called a multi client system, that is, it appears to work independently of every other business which is using it. Kind of application service providing. The way to do this is:

    Define a table containg each customer.
    Add an FK to your customers table in *every* data table that you have in your application. There you go, not a difficult design challenge, not much more overhead (one extra field per table), and no serious application / architecture defects.

  3. #3
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    London
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your reply, much appreciated. I do agree with you, have been concerned by the issues I raised and your information has further convinced me.

    Glad I haven't started developing the way I was going too...lol

    Thanks again.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •