SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Manchester
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation 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
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, here's a start to your table design, I've tried to keep it simple and limit it to a small number of tables but some of the types/subtypes could be in their own tables with Ids to link them.

    Code:
    Customers
    	Id
    	Name
    	Address
    	Email
    	PostCode
    	etc
    
    Users
    	Id
    	Name
    	UserType		CSM, Normal, Management, etc
    	Username
    	Password
    	etc
    
    Notifications
    	Id
    	CustomerId		From whom is this notification
    	UserId			Assigned User Id
    	NotificationType	Complaint, Compliment, Suggestion, etc
    	NotificationSubtype	
    	Details			the text the customer entered
    	NotificationDate	When the notification was received
    	TargetDate		When the notification needs to be addressed
    	Status			Resolved, Open, Escalate, etc
    	
    Actions
    	Id
    	NotificationId		Which notification does this action modify
    	ActionType		ChangedOwner, ClosedNotification, ChangedPriority, etc
    	Details			Text about the action, eg if type is ChangedOwner then this is the message from user1 to user2
    	ActionData1		Data specific to the action type, eg if it is ChangedOwner this field would be the previous user
    	ActionData2		Data specific to the action type, eg if it is ChangedOwner this field would be the current user
    	ActionDate		Date that the action happened
    	
    Notes
    	Id					
    	EntityId		To what item this note pertains
    	EntityType		To what type of item this note pertains, eg Action, Notification, User, Customer
    	NoteDate		Date the note was written
    	Note			The note

  3. #3
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Manchester
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Jim,

    Thanks for taking time to reply to my question. This will be of great help in starting my database design. I have one question thou.

    I've been reading some books about schemes and logical design and this was mentioned in response to a similar design and relates to inheritance......

    "First, you have two tables that store data about "persons". A person is an obect that you want to store information about, so you must need a table for it. A customer is a type of person. A user (or login user) is a type of person. Therefore, you need an attribute called "person type" or something like that.

    In my database, I would have only one table for person data. There are other tables that store role information (customer, staff, support operator) and other tables that store person attributes (registered, banned, unconfirmed)."

    And thats all it says, it just offers advice but doesn't actually tell you how to go about it or show an example!?!

    Has anyone got a take on this and whether it's worth doing?

    Regards

    Wealth-Builderz
    Last edited by Wealth-Builderz; Nov 21, 2004 at 11:38.

  4. #4
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Manchester
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Any further advice from anyone would be greatly appreciated.

  5. #5
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Wealth-Builderz,

    When you gave the example from the book about having different tables with a person's role or their attributes what the book is saying is that those things can be "Foreign Keys" to your user table.

    Like:
    Code:
    user_table
         user_id
         user_name
         user_phone
         role_id
         attr_id
    
    role_table
         role_id
         role_name
    
    attr_table
         attr_id
         attr_name
    What this does is it allows you to have multiple roles and attributes per user (if you wanted) and it makes it easier to add a new role or attribute and apply it to users.

    Does that make sense?

  6. #6
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You have to decide whether you will be storing enough information that is common to both Customers and CSRs to make it worthwhile. Also, if the role of a person can change (customer becomes a CSR) then it might be worth it.

    In your case I would say it's probably not necessary, up to you!

  7. #7
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Manchester
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Thanks again,

    Basically the examiners/markers would be looking for good design decisions and flexibility so I may well do this because it relates to 'generalization' and 'specialization' and it might serve me well to incorporate this at some level in my project and then later explain the benefits of this.

    I have tried this below, but I'm not convienct it's correct, I'd appreciate it if someone could please advise.

    Attempt Number Two.

    ========================
    Person_Table
    ========================
    Person_ID
    First_Name
    Last_Name
    Contact_ID
    PersonType_ID
    Address_ID
    Login_ID
    ========================

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

    ========================
    Login_Table
    ========================
    Login_ID
    Login
    Password
    ========================

    ========================
    Contact_Table
    ========================
    Contact_ID
    Telephone_No
    Email_Address
    ========================

    ========================
    Address_Table
    ========================
    Address_ID
    Street
    City
    Post_Code
    Region
    Country
    ========================

    Regards

    Wealth-Builderz

  8. #8
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Manchester
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation

    Been doing some reading and been given more design advice and I have made some more amendments to my tables.

    This design now permits a 'Person' to have only one set of login details (removed login table) but allows a 'Person' to have mulitple 'Contact' details, ie home, work, university ect (added cross-tab table). Also have grouped telephone and email attributes into contact details with address attributes as they all essentially fall under contact details.


    ========================
    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
    Post_Code
    Region
    Country
    Telephone_No
    Email_Address
    ========================

    ========================
    Person_Contact_Table
    ========================
    Person_Contact_ID
    Person_ID
    Contact_ID
    ========================
    Last edited by Wealth-Builderz; Nov 23, 2004 at 06:12.

  9. #9
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Don't forget a field for ContactLocation or something to that ilk so you know if a given address is for their Home, University, Office, etc.

    One thing about your design, and your use of a Persons table. Do you care what the address of a CSRep is? Will your customers need to log in with a username and password, or will they use their surname and postcode? Think about these before you commit to lumping Users and Customers together.

  10. #10
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Manchester
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmmm good points.

    *I will add a ContactLocation field.

    *A cutomer WILL need to login with their surname and postcode!!!

    *NO, I don't care what the address is of a CSRep but is it not good practice to keep the design flexible in case there is a need to in the future.

    I maybe wrong, I am learning as I go along. What would you reccomend with regards to the cutomer login issue??

  11. #11
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Put surname in username field and postcode in password field for them, in addition to storing them in the proper Surname and Postcode fields?

  12. #12
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Manchester
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Understood, thanks.. I'm gonna move on to designing the tables for the Notification side of the database.

    Attempt one is below: is along a similar line to the Person model as in a 'Person' can submit mulitiple 'Notifications'

    ========================
    Notification_Table
    ========================
    Notification_ID
    Person_ID ****** unsure *******
    NotificationType_ID
    Details
    Notification_Date
    Target_Date
    Status_Type
    ========================

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

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

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

    Would the single Person_ID field in the Notification table be satisfactory as it is needed in finding both the Customer (their Person_ID) who submitted the notification AND the Resolver (their Person_ID) who is assigned to it?? In essence I would need to store two Person_ID numbers in the Notification table linking both Users to the individual notification, but that would be stupid and lead to possible redundant data. Thats the bit I'm unsure about.

    Sorry if I ask to many questions, just want to get the design spot on.

  13. #13
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Manchester
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Thanks for all your advice Jim, the database is sort of taking some reasonable shape now, I hope. The one remain question I have (for now) is with regards to the stored standard letters that are to be automatically generated and emailed or posted to the person who submitted the notification. How would this be modelled in a databse design??


    ========================
    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
    Post_Code
    Region
    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
    Details
    Notification_Date
    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
    NoteType_ID
    Note_Details
    NoteDate
    ===================

    ===================
    NoteType_Table
    ===================
    NoteType_ID
    NoteType
    ===================

    Any more advice and/or criticism from anyone would be most welcome.

    Regards

    Wealth-Builderz

  14. #14
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would make 2 tables:
    Letters
    - Id
    - Name - "Letter to new subscriber"
    - Version - "1.0"
    - IsCurrentVersion - Y/N
    - Text - Text of the letter, with some kind of replaceable variable identifiers for name, address, etc eg "Hello %%FirstName%% %%LastName%%". Or this could be a pointer to a text file or word document.

    LettersSent
    - LetterId
    - PersonId
    - ContactId - link to contacts table
    - SentById - In case you need to store the id of the rep who sent it
    - DateSent
    - DeliveryMethod (Email, Post)

    I think that's all you'd need.

  15. #15
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Manchester
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Thanks again Jim,

    Right, fifthteen tables later and this is the current database design which is to store the relational data mentioned in my requirements. What does everyone think, is this a suitable, workable design?


    ========================
    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
    Post_Code
    Region
    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_Date
    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
    ===================


Bookmarks

Posting Permissions

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