Hi All,



We have different Mkt Research Business Units (BU) (Study Types A, B, C) for which i want to create different databases.

P.S: Please see attached powerpoint deck in zip which is self-explanatory.
These BU have some common fields tables Like Study table, Login table which i want to put as one Main Study Database. There are also Lookup fields (for dropdowns) which are common to these BU (Study types), which i want to put in another Database called the Lookup Database. So the all the 3 databases are linked with the Study Database and the Lookup Database is in turn linked to each of these 3 Study Type databases. I want to split all these 5 databases into :
1] an compiled Application Layer which can be distributed to each user &
2] a Data Layer which will be centrally located on a dedicated server.

Process:
1] User opens his Application Layer and it shows Login form. User Logins.
2] Study Type form appears, which asks user to choose Type of Study (A, B, or C).
3] on choosing Study type, specific Database is opened & user is shown Study Form (which will have a field called Study Type).
4] along with Study Form, a sub-form for that particular Study Type will be shown which will have that Study Type specific fields.
5] A separate section for Reports should be there.
6] Forms for Study-Review team to review accuracy of study details entered by User should be present, so that after their review, Study information can then posted to Permanent tables (an OK flag can be set).

What i want to know is :
1] is this possible & will this work for 2000 users? what server/database factors/specifications will support that many users?
2] if so, can i create forms & reports based on the tables in all these databases?
3] do i need to create temporary tables which will hold user entered data, until it is reviewed (OKed) by Review team & posted into permanent tables?
4] What are the documents i need to create for an initial database design?

has anyone tried creating something like this and if so, can that person help/guide me as its an urgent requirement?

thanking in advance.