Database Design Overview 1

[Tezdread]

Hi all,

We have quite a few access databases that have been create by different people / departments. I now have the task of reviewing all these and making them all consistent.

I'm currently wrtting up some design guidelines and would like any advise you have.

I know what the colours, fonts etc are as these will follow the companies brand and these will only be on the forms and reports.

I have highlighted Naming Conventions so that all table names start with tbl forms start with frm etc.

Other things I think I need to include are:
Linking tables to a backend database
Relationships (need more info on the best/correct way)
Security & Maintenance

The guidlines will then be followed when creating any new databases or amending the existing ones so it has to be very generic.

Thanks in advance

Tezdread
"With every solution comes a new problem"

 

[BNPMike]

What you always need to do is to define your objectives eg to run faster, to be more user-friendly, to conform to corporate standards etc etc. If you don't have any such contraints then don't do anything. More money is wasted in the World trying to upgrade technology for no reason than is wasted on the War in Iraq.

Well possibly not....

 

[Tezdread]

The objectives are clear:

1. Produce a report detailing all the databases that will be kept and what changes need to be made (no problem)

2. Make all databases fit in with the companies branding and to be user friendly(no problem)

3. Improve performance and stability on all databases (some big issues / modifications)But this will be normalising data splitting databases etc

4. To secure the databases to stop people going in and changing / deleting information or objects

5. To produce update procedures and information documents showing details about what the databases contain.

I suppose what I'm looking for are some development/design guidlines to help me in the areas of improved performance/stability and security. Things like, is it better to have user access setup and or to have different front ends for different departments and one backend.

I don't know exactly just some advise to comfort before embarking on this perilous task

Tezdread
"With every solution comes a new problem"

 

[Tezdread]

On the subject of security, would it be better to use the user group access / permissions or to have separate front ends?

E.g I split the database into say 3 front ends and one backend, and each of the front end DB's are stored in secure locations (NTFS Permissions) then the front end forms will only have the required fields that the particular user requires.

Or can this be done better using login permissions etc?

Tezdread
"With every solution comes a new problem"

 

[willir]

All good ideas. Things that may have been missed in the above...

- backup policies
- development and deployment policies
- documentation of ownership
- change control where future developments are formally discussed / reviewed by a change control group (thus, the group can ensure a) deployment / backout process is satisfactory, b) new policies are being followed
- share a central tables such as customers / suppliers / contact. No need to have 6 or 12 contact tables floating around.

Richard

 

[Tezdread]

thanks willir, all good points. where you say dev & deployment policies, what do you mean?

Tezdread
"With every solution comes a new problem"

 

[willir]

When somebody either developes a new database or improves an existing database, you should consider adopting a methodology or check list for deployment.

When developing a database, you have to make sure the developer does not compromise the data while "playing".

Some of this may seem like over-kill but just yesterday, a local developer wiped out over two years of history in a database when they were deploying an "upgrade".

When a developer looses their database, they loose their work and knowledge invested into the design and development -- One person, dozens or 100's of hours. When a company looses a database, they loose the information in the database that can represent a very considerable investment made by numerous users with ayears of history.

Protect the data first.

 

[Tezdread]

Thanks millir, will work on getting all this into the plan

Tezdread
"With every solution comes a new problem"