The Customers entity is a list of citizens in your administrative area. For this version of the system it includes their address thereby assuming that a customer will not have multiple addresses. Our internal system keeps addresses as a separate entity and there is an argument for moving the address to it's own entity or even perhaps using the Geo Directory for properties instead of creating yet another list of them. Another option would be to allow for a customer to have multiple properties (work, home, other) but before going down this road it would be important to have a clear idea of why this would be beneficial. Should a customer really be linked to both a private home and a business? Are they not two separate areas of customer relations? One major gotcha here is duplication. How to force users to do a proper search before adding a new customer/address? With our current system duplication is a major problem and it boils down to the ease with which a user can create a new entry. They can do a quick search but if it turns up nothing they will simply add a new customer and in many cases a more stringent search would have turned up the existing entry. It's difficult to enforce users to make thorough searches when they are working through many enquiries under time constraints.


We use departments as a direct mapping to our internal departments. Internally we already have a list of departments in another database so our implementation would integrate with that instead of having this separate table. It's also important to not delete departments as they become defunct so that historical data can be retained. An extra column/property, IsActive for example, could be used to control which departments appear to users while enabling the system to keep redundant departments for statistical and historical reasons.


The enquiries entity/table is the primary entity (perhaps customers would be similarly important) in the system. There are essentially 2 main types of enquiry. 'Resolved at First Point of Contact' and 'For Follow-Up'. Those dealt with immediately, often at our Front Desk, would be entered as the former and those that need to be passed on to a user best placed to deal with the issue are the latter. A subset of these 2 main types would be then whether they are Anonymous or associated with a Customer. Beyond that one other main distinction exists. That of Fix Your Street incidents. Fix Your Street incidents fall under the main distinctions in that they are automatically anonymous and for follow up.

The important consideration for users with Fix Your Street is to consider the language used when adding comments or closing since the content will be seen by the public.

Going further there is an option to link an enquiry to a Rep (representative) or Council Member. SDCC's CCS goes further having a completely separate system for Members to submit their enquiries and we must then integrate with it in the same way we integrate with Fix Your Street. We then force users to go through a process of authoring official responses and having them approved by a SEO/AO before closing the enquiry. Since we didn't develop the members system internally we removed those parts of the system relating to Members but kept the original method of allowing the user to enter an enquiry on behalf of the member (by selecting from a list of reps).

Every other entity/table in the system except Users and their related tables exists as a property of the Enquiry including users in terms of referrals, who submitted it and so on and I won't go into detail about them except for the following.


While actual emails are sent immediately when a user registers or requests their login details any email associated with a specific enquiry are instead saved here and the poller then does the job of sending them. This allows for any problems that might occur with the email server be it unplanned or planned downtime. It's helpful to have a record of all emails sent in relation to an enquiry and to have a certain redundancy in terms of ensuring they actually do get sent.


Like emails these tables will stored data that needs to be sent to Fix Your Street (in the case of comments this will not always be the case) and like email this offers a certain protection against problems that might occur if we tried to send these messages immediately so it's worth having this small fall back.


The user system is straight-forward except perhaps for the idea of groups. Groups simply exist to allow for statistics or searches to be done over a particular area, Front Desk staff for example, as opposed to a department etc. It most closely matches the idea of sections within a department but would not be restricted to that.

Again at SDCC we have staff data in another database and we integrate our user table in the CCS with this data so we would have a StaffId column in the user table that matches the id of our staff list elsewhere. Then, for example, their email address would be pulled from the staff list instead of the user table.

Extending Entities/Tables

With any of these entities or tables you may wish to extend or integrate with your own systems and a good rule of thumb would be to do so without changing the existing structure. For example if you want to use a staff table in another database then rather than adding a staffid column/property to the user table/object it would be best to create a new table in the CCS to do this. A UserStaff table with UserId and StaffId columns and adding a StaffInfo object to the User object in the system for example, This would make it much easier to pass on your extensions to other local authorities where they simple have to add these extra tables and update the corresponding code. Obviously in certain cases it makes more sense to simply add the column to the existing table (the IsActive example for departments would fit here) but give some thought to it before making big changes.

Last edited Nov 16, 2012 at 11:31 AM by SouthDublinCoCo, version 5


No comments yet.