Database Tables Advice

Hi all,

At the moment I am trying to model the system data before making any concrete database decisions as I want to form a conceptual and logical model before moving onto the physical implementation of my database. Has anyone got any advise for me with regards to the sort of tables I would need to model my data. Below are the user requirements I am working from but I fear I am missing some, at the moment I have a Customer, Notification and Employee table but this seems a small number. Any advice would be welcome.

4 User Requirements

4.1 The Customer Interface

Customers should have an easy way of accessing their details by entering their surname and postcode. There should be a facility for them to enter their details if they do not appear, as new customers may not yet be on the system.

Customers should have a free-text area to type in details of their notification. They should be encouraged to select from a list of types and sub-types the main description which applies to the notification.

If there is no recorded email address for the customer on file they will be asked if they want to input one.

Once the notification has been submitted the customer should be thanked for taking the trouble to communicate and told that an acknowledgement will be sent within one working day by email or post (if they don’t have an email address)

4.2 The Customer Service Interface

Submitted complaints are automatically entered into the database. Every day, the customer services manager (CSM) will analyse all notifications received. A list of the complaints should show on her system at login.

The CSM will select the notifications one at a time and may adjust the types allocated by the customer. She will decide if the notification is a complaint, compliment or suggestion and allocate the appropriate category. She will then choose an acknowledgement letter from a pre-defined list of Word documents – the letter will be generated in Word with the customer’s name and address automatically entered. The acknowledgement will either be printed for posting or sent by email if the customer has an email address.

If the notification needs no further action, the CSM will mark it as resolved. If it needs further action she will allocate it to one of the team of Resolvers (i.e. people working at the different agencies), she will give it a turn-round date (a default has been agreed at 5 working days but this can be over-ridden) All notifications allocated to a Resolver will show on a list when s/he logs in. For each notification s/he should either:

Deal with it and contact the customer saying what has been done – stored standard letters may be used for communications with members

Pass it on to another Resolver with a note of what action is required

If the Resolver considers the notification has been dealt with s/he should mark it as resolved.

Every action taken relating to a notification should be recorded in an Actions list in the database.

4.3 The Reporting Interface

Management would like access to a number of standard reports:

All notifications by category and type

Outstanding notifications by category and type

Outstanding notifications by Resolver

A summary table showing numbers of complaints by type and sub type over a given period

Regards

Wealth-Builderz