SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Location
    Hailsham, UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Several instances of a database application?

    I have a completed database application. It is a catalogue manager for our clients to use.

    What is the best way to do duplicate this whole application for multiple, independant installations -- without using more than one database?

    They are all controlled (administered) from one place.

    I have thought about two options:

    1) add an installation_id column on all relevant tables
    instead of
    catalogue_item(item_id, name...)
    catalogue_item(installation_id, item_id, name...)

    2) replicate tables by using table name prefixes; add a table to record which prefixes corresponds to which installation
    instead of
    catalogue_item(item_id, name...)
    bobs_catalogue_item(item_id, name...)
    Problems were:

    1) Seems like I'd be messing up an otherwise normalised db structure - I would need to remember to add the installation_id check on many queries - risk of accessing data belonging to another installation if installation check was missed out (security problem)

    2) Seems like the wrong choice - schema would be growing - requires special table managing code - changes to the structure would have to be applied to all duplicated tables. However, queries would not be made more complicated as they would in (1).

    ---
    (I had been doing this using databases for each installation but our ISP is not allowing database connections outside of their network - a problem because we have clients hosted with different ISPs)
    Thanks for your attention

  2. #2
    SitePoint Addict StephenBauer's Avatar
    Join Date
    Apr 2004
    Location
    USA
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is this catalog manager a web app (basically an ASP offering) that various clients will use to manage their catalog by all going to the same URL and logging in? If it is intended to be accessed like an ASP application, then option 1 makes sense. Not every table would need an installation_id (actually think of it as a client_id) added to it though if relationships are set up properly. You will have to change your queries/stored procs to include the use of the installation_id (aka client_id) to restrict data access to the appropriate logged in user.

    I would steer clear of trying option 2. You will create many headaches for yourself and/or your administrators.

    Quote Originally Posted by calkie_f
    I have a completed database application. It is a catalogue manager for our clients to use.

    What is the best way to do duplicate this whole application for multiple, independant installations -- without using more than one database?

    They are all controlled (administered) from one place.

    I have thought about two options:

    1) add an installation_id column on all relevant tables
    instead of
    catalogue_item(item_id, name...)
    catalogue_item(installation_id, item_id, name...)

    2) replicate tables by using table name prefixes; add a table to record which prefixes corresponds to which installation
    instead of
    catalogue_item(item_id, name...)
    bobs_catalogue_item(item_id, name...)
    Problems were:

    1) Seems like I'd be messing up an otherwise normalised db structure - I would need to remember to add the installation_id check on many queries - risk of accessing data belonging to another installation if installation check was missed out (security problem)

    2) Seems like the wrong choice - schema would be growing - requires special table managing code - changes to the structure would have to be applied to all duplicated tables. However, queries would not be made more complicated as they would in (1).

    ---
    (I had been doing this using databases for each installation but our ISP is not allowing database connections outside of their network - a problem because we have clients hosted with different ISPs)
    Thanks for your attention
    Last edited by StephenBauer; May 26, 2004 at 10:25.

  3. #3
    SitePoint Wizard bronze trophy JRMillion's Avatar
    Join Date
    Apr 2004
    Location
    Arlington VA
    Posts
    2,094
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you could use a session variable to mark what installation is being used, then you would need to add like a "installID" to your DB rows.

    and add to end of queries WHERE installID = $_SESSION['installID']

    or something to that effect.

  4. #4
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Location
    Hailsham, UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StephenBauer
    Is this catalog manager a web app (basically an ASP offering) that various clients will use to manage their catalog by all going to the same URL and logging in?
    Yes, exactly! And yes, rather than installation_id it will be linked directly to the client who is logged in managing it.

    JRMillion - yep, I will be doing something similar to that

    So I will now try and add the client_id field to a sensible place... hopefully it will be smoother and not affect as much as I thought

    Thanks for setting my mind at rest!
    Ian


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
  •