First step to design Database?

[suklee64]

We currently have corporate level Oracle DB server in corporate headquarter. And corporate level decided us (Business Analysts) to limit our accessing DB, because of the burden of data transaction. Therefore, we decided to make a small MS Access DB into network drive, download monthly some parts of Oracle DB, and all business analysts can share the small sized Access DB.

Now, my concerning is how to design the small DB which all department analyst can share with. Because all analysts from different department usually select different column in different tables from the huge Oracle DB, the success of my Access DB design comes from everybody’s satisfaction.

I wonder if DB specialist in this group can provide me the guide line/ steps/ awareness before design the DB. What is the first step to design DB? Are there any tools to help DB design?

Thanks,

 

[Pat Hartman]

The first part of the application will be to automate the monthly import. After that, you need to know what the users want if you are going to provide pre-built reports. I would suggest a shared database with the pre-built reports linked to the "data" database and then allow each user to create his own database that will be linked to the downloaded data. They can then create their own custom reports without interfering with each other. If they develop reports that they want to share, you can import them into the shared database. Under NO CONDITIONS allow the users to create objects in the shared database. This is the path to corruption. Plus, Access will not allow sharing a database for object changes unless you are using SourceSafe.

 

[suklee64]

Dear Pat Hartman;

Thank you for the reply. It will really help my current project. However, I need your verification for pre-built report and “Data” database. Is this same meaning of shared database? I just want to understand your suggestion correctly.

Many thanks,

 

[Pat Hartman]

Given that Access applications should be separated into front ends which contain forms/reports/queries and all the code and just links to the back end and the back end database contains only tables which hold the data.

So the "data" database is the be and it will be shared. Each user should have a personal copy of the fe located on his hard drive and any user who wants to create his own reports should have a private database that links to the shared be.

 

[KenHigg]

FYI: This is commonly known as a datawarehouse. A lot of larger companies have their IT dept set these up -

 

[gemma-the-husky]

presumably, your main areas of concern will be to decide

a) what data fields you need in your access database
b) what is the "atromic level of these" - eg do you intend to totalling certain transactions and storing them as weekly/monthly totals, as doing stuff like this will reduce the size of the Access DBs, and increase speed of access, but will also restrict the level to which you can drill into your data