What structure of database to be used?

[Geirr]

Hi.

I have a question I qould like to have some input on. The case is that I am not sure which structure of databases/tables suits me (and the organtisation) best.

The scenario is:
Our orgainisation are working on projects. Each projects will hold somwhere between 6000 to 8000 records. Each projects will have around 4 to 8 users (like teams). Each projects have som few common informations, but are mostly total separatly. As for today, it's about 15-20 projects using Access. There are creating new projetcs continously, and there are working on about 6 - 8 projects simultaneous.

As for today, I have a solution with a Front End and a Back End for each projects (user links to desired project) based on standard Accsess 2010.

For several reason, I would like to migrate to MS Sql Server, but I am not sure if I should creat one database for the whole organisation, or use separate database for each project. I see the benefit for both ways, and for me as an administrator, it will not make any difference. (at least - my job is not the issue here...)
But, I am afraid if somebody could brake the database some way - it's better to brake one project, and don't influence on the other projects. (datarepairing, restoring and so on...) (based on Access experiences....)

I would be glad if I could get some comments, experiences on similar choises.. Also some clear advises are very commendably.

Best Regards.

 

[MarkK]

The information you provide is not specific enough. What is a project? Maybe one project is to open a school. Maybe another project is to do a construction job. OK. So those are very different databases.

How many records you have is not relevant to the structure of database. How many users you'll have is not important to how to structure a database. Maybe you have one user who consumes 1000 minutes, or you have 1000 users who each consume a minute. Same thing(ish).

SQL Server is always Client/Server architecture, so SQL server will always have an FE and a BE. I would not have an FE and BE for each project if the tables in all the projects are the same. In that case I would have one BE, and then you can query all your projects very easily and determine which is most efficient and why. Figure out which is most profitable. To split up each project, if it has the same structure, to multiple databases limits the usefulness of your data for analysis purposes.

Does that make sense?
Mark

 

[mdlueck]

I am up-scaling a database application which was created about 13 years ago. The legacy app is an Access .MDB file shared on the file server. No separate FE.

Each product to be tracked requires its own separate copy of the database, as it was not developed multi-product aware. There are perhaps 100 instances of the legacy app floating around this organization.

Up-scaling to Client / Server. A disposable Access FE database. The SQL BE DB now provides many shared tables such that someone only has to enter information once and multiple products / parts / etc... may link to the same shared record. Aka products share parts / parts share tools used to make the part / and so on.

If you are going to the trouble to make the BE DB a SQL one, then go ahead and design the application such that one instance may be shared, and not require a separate instance be maintained for each xyz entity.