need expert opinion on database design

[sqlpro]

Hi friends

need idea on database design
we are moving our application from foxpro database to sql server.
we decided that we'd using MSDE for some clients as they cannot efford a full sql server license.

we have our data categorized in 3 types.
1)Client
2)our company specific
3)System-Lookup

and data in category 2 chnage with our every new build release.
my question is

do you think that creating separate databases using the Client/our company specific/System-Lookup split is actually going to work? If we can't go over 2Gb per database with MSDE then there are a few alternatives:

1) We just go with that split
2) We split the 3 categories up further so that tables like MASTER are in their own database so they can grow to 2Gb
3) We don't worry about the MSDE implications and put all tables in one database.

Of those tables that are not in the Client sheet(some tables exist or dont exist depending on client), we need to be thinking about the ways in which we're going to send updates out.

cheers

 

[Mike Lewis]

Sqlpro,

It's difficult to give good advice without knowing more about your specific setup and requirements, but, in general, it does seem to make sense to split the client-specific structures into a separate database. It'll make distribution and maintenance a lot easier.

I'd be cautious about splitting up databases merely to overcome the 2GB limit. If that limit is likely to be breeched, perhaps you shouldn't be using MSDN, although I accept that there are economic factors affecting that decision. Just think of the hassles we have with the 2 GB limit in VFP. One of the great benefits of SQL Server is that you should be able to leave those concerns behind.

These are just my initial thoughts. I'd be interested in hearing what others say.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[sqlpro]

Yes,Mike
u r right in ur opinion.we have some tables that can b very
huge.and also we've some clients who cannot pay full sql server license.so we r thinking of splitting databases ofcourse mostly for overcoming 2gb limit.
One of the important reasons we moving to sql server is corruption in vfp data.we almost reacv atleast 1 call everyday abt data corruption.

We could say this migration (from VFP to sql server) mainly
our company decision to overcome many of these hassales.
so we cannot force our clients to buy full sql server.
Thanks for post.

cheers