>First, let me describe the scenario
>
>There's an accountant (bean counter).
>The accountant has customers.
>Each customer has several years for which books are kept
>
>In foxpro I had a root folder, containing a folder for each customer, each containing a folder per year
>
>
>Some customers do their own bookkeeping - to a certain extent - mail the 'year' to the accountant who may make some changes and mail it back. The customer's database is readonly whilst the accountant has the year's database - and vice versa
>
>So I'm thinking to do the same in .net with sqlserver
>
>Mail: I think I can backup/restore the database in sqlserver
>
>Sql server: The accountant would have an sql server whilst the customers would access the database with the native drivers.
>The sql server can be 'told' to do certain things - like attaching or detaching databases
>
>There are many customers - installing/maintaining an sqlserver service is not feasible in terms of work/problems. They have a 'light and simple' accountancy software - nothing very fancy. It runs without (too many) problems in foxpro and I would like to keep it that way.
>
>Server Access: I think I cannot work with Windows authentication and need sql server authentication. Each database will be created with a specific owner, ie the same for all databases. The system has to be simple and transparant for the users
>
>
>One of the thoughts was to put all the years of one customer into a single database. But that would not make things simple to exchange a part (year) between the accountant and the customers - would it ?
>
>
>Does this sound like the 'right' approach ?
>
>
>
>Thanks,
Hi Gregory,
For me using just one database sounds like a good approach. I remember I was having a similar problem in 2003 when I needed to create a SQL Server database for something we had in FoxPro. We had databases for each state (MA, RI, CT, NH) and I was thinking of creating a SQL Server database for each state as well. Luckily I called Sergey that time and he helped me to figure out that SQL Server is a completely different storage and you don't need to worry about 2GB limit, etc.
If it's not broken, fix it until it is.
My Blog