Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    29

    Exclamation Unanswered: Database Design Advice

    Hi,

    I have come up with a project to design a web-based Complaints and Suggestion System for a local estate agents in my area and I am going to use PHP with a MySQL database and at the moment I am doing all my Process and Data Analysis before moving onto to the conceptual and logical design of my database.

    I've been asking various professionals for advice and guidance as I am currently on a first going into this year and want to produce an excellent project.

    Be interested to have your input or any ideas you may have about it. I'l attach my Project Requirements that I have come up with after my research but any advice on table structure at this point would be most helpful.

    My User Requirements are as follows.....

    (1)The Customer Interface

    * Customers should have an easy way of accessing their details by entering their surname and post code. 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 e mail 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 e mail or post (if they don’t have an e mail address)

    (2)The Customer Service Interface

    * Submitted complaints are automatically entered into the database. Every day, the customer services manager (CSM) will analyze 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 e mail if the customer has an e mail 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.

    (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

    Any help or advice would be very welcome.

    Regards

  2. #2
    Join Date
    Oct 2004
    Posts
    29

    Exclamation

    Right, here's the first attempt to model the database. Any advice or mention of things I should incorporate that I have missed would be extremly helpful.


    Customers

    Customer_ID
    First_Name
    Last Name
    Street
    City
    Post_Code
    Region
    Country
    Telephone_No
    Email_Address


    Users

    User_ID
    User_First_Name
    User_Last_Name
    User_Type
    Login
    Password


    Notifications

    Notification_ID
    Customer_ID
    User_ID
    Notification_Type
    Notification_SubType
    Details
    Notification_Open_Date
    Notifiaction_Close_Date
    Target_Date
    Status

    Actions

    Action_ID
    Notification_ID
    Action_Type
    Details
    Action_Data_1User
    Action_Data_2User
    Action_Date


    Notes

    Note_ID
    Entity_ID
    Entity_Type
    Note_Date
    Note


    Best Regards

    Wealth-Builderz

  3. #3
    Join Date
    Oct 2004
    Posts
    29

    Exclamation

    Any advice and/or criticism would be welcome


    Second Attempt:

    ========================
    Person_Table
    ========================
    Person_ID
    First_Name
    Last_Name
    Username
    Password
    PersonType_ID
    ========================



    ========================
    PersonType_Table
    ========================
    PersonType_ID
    Person_Type
    ========================



    ========================
    Contact_Table
    ========================
    Contact_ID
    Contact_Location
    Street
    City
    Region
    Post_Code
    Country
    Telephone_No
    Email_Address
    ========================



    ========================
    Person_Contact_Table
    ========================
    Person_Contact_ID
    Person_ID
    Contact_ID
    ========================



    ========================
    Notification_Table
    ========================
    Notification_ID
    Person_ID
    NotificationType_ID
    NotificationSubType_ID
    Notification_Details
    Notification_OpenDate
    Notification_CloseDate
    Target_Date
    Status_Type
    ========================



    =======================
    NotificationType_Table
    =======================
    NotificationType_ID
    Notification_Type
    =======================



    ==========================
    NotificationSubType_Table
    ==========================
    NotificationSubType_ID
    Notification_SubType
    ==========================



    ========================
    StatusType_Table
    ========================
    StatusType_ID
    Status_Type
    ========================



    ========================
    Person_Notification_Table
    ========================
    Person_Notifications_ID
    Notification_ID
    Person_ID
    ========================



    ===================
    Action_Table
    ===================
    Action_ID
    Notification_ID
    ActionType_ID
    Action_Details
    ActionDate
    ===================



    ===================
    ActionType_Table
    ===================
    ActionType_ID
    ActionType
    ===================



    ===================
    Note_Table
    ===================
    Note_ID
    Note_Details
    NoteDate
    ===================


    ===================
    Letter_Table
    ===================
    Letter_ID
    LetterType_ID
    Letter_Details
    ===================


    ===================
    LetterType_Table
    ===================
    LetterType_ID
    Letter_Type
    ===================


    ===================
    LetterSent_Table
    ===================
    Letter_ID
    Person_ID
    Contact_ID
    Date_Sent
    Delivery_Method
    ===================
    Last edited by Wealth-Builderz; 12-01-04 at 14:46.

  4. #4
    Join Date
    Oct 2004
    Posts
    29
    Are there no database designers out there who can comment or make suggestions on this design. I'd be very appreciative.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •