Best practice

Hi,
I'm writing an application using a sql server database. In this database
there roughly 3 kinds of tables:
1. tables containing data which is generated during the run of an
application
2. tables containing data entered by our clients
3. tables containing data entered by our compagny.
Twice a year our clients need to receive new data in table group 3. Thereby
the data stored in table group 1 may be removed.
Only the data in group 2, entered by our clients should stay in the
database.
What's the best practice to achieve this goal?
I was thinking in splitting it up in 2 databases, 1 with group 1 tables, and
1 with group 2 and 3 tables. Twice a year we create a backup of this second
database en our clients restore this database. After this the clients need
to run a program to check the integrity.
Is this the way to go?
Thank's,
PerryOn Tue, 11 Apr 2006 15:36:42 +0200, Perry van Kuppeveld wrote:

>Hi,
>I'm writing an application using a sql server database. In this database
>there roughly 3 kinds of tables:
>1. tables containing data which is generated during the run of an
>application
>2. tables containing data entered by our clients
>3. tables containing data entered by our compagny.
>Twice a year our clients need to receive new data in table group 3. Thereby
>the data stored in table group 1 may be removed.
>Only the data in group 2, entered by our clients should stay in the
>database.
>What's the best practice to achieve this goal?
>I was thinking in splitting it up in 2 databases, 1 with group 1 tables, an
d
>1 with group 2 and 3 tables. Twice a year we create a backup of this second
>database en our clients restore this database. After this the clients need
>to run a program to check the integrity.
>Is this the way to go?
Hi Perry,
I'd prefer to keep all data in just one database. That makes it much
easier to maintain integrity (FOREIGN KEY constraints don't work
coorss-database), plus it will probably yield better performance.
For your half-yearly update of the company-supplied tables, I'd
recommend that you distribute a script to your customers. The script
would either be a .SQL script with INSERT, UPDATE and DELETE statements,
or a .CMD file with a series of SQLCMD and BCP statements, plus the
files to be used in the bcp operations.
Hugo Kornelis, SQL Server MVP|||like Hugo said, one database for sure.
staging tables to import/export data. good names for all tables.
stored procedures to load/run the data in and out.
you need to make a backup before and after all the data movements.