Database setup for a suite of applications

microsoft.public.sqlserver.programming

1 Jul 2005 11:19 AM

Craig HB

We are developing a suite of web applications for restaurants, which include
an inventory app, cash-up app, staff timesheets app etc. The applications are
built with ASP.NET and the database is SQL Server 2000.

All these applications use application-specific tables (like the Timesheets
table for the Timesheets app) and shared tables (like the Restaurants table
which they all use). The way we have dealt with that is to put all the tables
in 1 database (called RestaurantManager) and let the different applications
all use that. This works quite well except that as we develop more
applications the amount of tables and stored procedures grow and the database
can get a bit confusing as different developers work on different
applications. Also, as we add more restaurants onto the database, performance
is suffering, and might have to move various parts of the database onto
different servers in the future.

So, we are considering using a different database for each application. What
I am not sure about is how to deal with the shared tables. Tables like
Restaurants are needed by all applications, whereas tables like SaleItems are
used by some of the applications.

Should we used linked views, or should each database get a Restaurant table
and then we synchronize the data ? And if we do that, how do we synchronize :
replication, triggers, written into the stored procedures that make the
updates ?

Any advice on how to deal with this situation would be appreciated.

Thanks,
Craig

1 Jul 2005 12:37 PM

Stu

<snip>
Also, as we add more restaurants onto the database, performance
is suffering, and might have to move various parts of the database onto
different servers in the future.
</snip>

That one statement is reason to consider replication instead of linked
servers. Anytime you have an application spread across multiple
database servers, you must consider how you will handle network
outages. Replication gives you an option to handle that; otherwise,
you'll need to build in some sort of synchronization into every stored
procedure that will touch the data on multiple servers.

Just my thoughts,
Stu

1 Jul 2005 1:10 PM

Rudi

Hi Craig,

As it seems that you are working on an ASP (Application Server Provider)
model, hosting the application for your clients, it seems more logical that
you would have 1 database per client. I really see a lot more benifits in
having 1 database per client than 1 database per app.

This allows you to backup/restore per client, optimize per client, move the
database to a different server in case of performance, track usage (bill per
usage? per GB used?), clean security, no need for data-level security, etc.

Using a naming convention, for a clean separation in tables, stored
procedures, etc.
e.g. t_rest_%, p_rest_% for restaurant app, t_time_%; p_time_% for the
timesheets app; etc.
can really help for the developers.

just my 0.02?

Show quote

"Craig HB" <Crai***@discussions.microsoft.com> wrote in message
news:D06FD751-4C40-4A26-A785-1E05667C2D47@microsoft.com...
> We are developing a suite of web applications for restaurants, which
> include
> an inventory app, cash-up app, staff timesheets app etc. The applications
> are
> built with ASP.NET and the database is SQL Server 2000.
>
> All these applications use application-specific tables (like the
> Timesheets
> table for the Timesheets app) and shared tables (like the Restaurants
> table
> which they all use). The way we have dealt with that is to put all the
> tables
> in 1 database (called RestaurantManager) and let the different
> applications
> all use that. This works quite well except that as we develop more
> applications the amount of tables and stored procedures grow and the
> database
> can get a bit confusing as different developers work on different
> applications. Also, as we add more restaurants onto the database,
> performance
> is suffering, and might have to move various parts of the database onto
> different servers in the future.
>
> So, we are considering using a different database for each application.
> What
> I am not sure about is how to deal with the shared tables. Tables like
> Restaurants are needed by all applications, whereas tables like SaleItems
> are
> used by some of the applications.
>
> Should we used linked views, or should each database get a Restaurant
> table
> and then we synchronize the data ? And if we do that, how do we
> synchronize :
> replication, triggers, written into the stored procedures that make the
> updates ?
>
> Any advice on how to deal with this situation would be appreciated.
>
> Thanks,
> Craig
>

1 Jul 2005 1:24 PM

If the databases are on the same server, you can reference them by adding
the db name prefix. For example, if you have an INVENTORY database and a
CASH database:

select
    i.*,
    c.*
from
    inventory..inv_detail as i
    join
        cash..cash_detail as c
        on c.x = i.x

Slitting each records for each restraunt into seperate databases or tables
could complicate maintenance and keeping data structures synchronized.
However, you may want to develop a process that migrates historical
transactions into a seperate database or server at regular intervals.
Depending on your reporting requirements, you may only need timesheet and
inventory detail transactions going back for 1 year.

Show quote

"Craig HB" <Crai***@discussions.microsoft.com> wrote in message
news:D06FD751-4C40-4A26-A785-1E05667C2D47@microsoft.com...
> We are developing a suite of web applications for restaurants, which
include
> an inventory app, cash-up app, staff timesheets app etc. The applications
are
> built with ASP.NET and the database is SQL Server 2000.
>
> All these applications use application-specific tables (like the
Timesheets
> table for the Timesheets app) and shared tables (like the Restaurants
table
> which they all use). The way we have dealt with that is to put all the
tables
> in 1 database (called RestaurantManager) and let the different
applications
> all use that. This works quite well except that as we develop more
> applications the amount of tables and stored procedures grow and the
database
> can get a bit confusing as different developers work on different
> applications. Also, as we add more restaurants onto the database,
performance
> is suffering, and might have to move various parts of the database onto
> different servers in the future.
>
> So, we are considering using a different database for each application.
What
> I am not sure about is how to deal with the shared tables. Tables like
> Restaurants are needed by all applications, whereas tables like SaleItems
are
> used by some of the applications.
>
> Should we used linked views, or should each database get a Restaurant
table
> and then we synchronize the data ? And if we do that, how do we
synchronize :
> replication, triggers, written into the stored procedures that make the
> updates ?
>
> Any advice on how to deal with this situation would be appreciated.
>
> Thanks,
> Craig
>