Communication followup database design

database-design

I'm trying to decide on how to set up the tables to get this use working without over-complicating it or not collecting enough data.

For the sake of an example, I've got a ticketing system with two users, a manager and a worker.

A ticket comes in, and the manager assigns it to a worker.
The manager also can set when they expect the the ticket to meet any particular milestones.
The worker updates ticket status whenever the status changes.
If the worker does not update the ticket to the status that the manager defined by the set date, the manager is notified (and the worker gets a mild electric shock).

The goal is to generate notifications when a status update does not happen by the set deadline. The second part is to be able to gauge performance, so if the manager keeps updating the date expected update (for a set status), the data is there to generate such a report.

data I'm trying to track

Ticket
User
Current Status
Previous Statuses
Expected update
Previous Expected updates

the list of statuses is likely to change, so using multiple columns won't work

I could store all of these in one table, but I could lose history of status updates.

I'm looking at a setup where I have a table for a ticket, ticket_status and ticket_expected with 1:m relationships (1 being ticket).

I've also got the latest ticket and next expected update on the ticket to make lookups more straight forward.

Is there a better way to do this?

enter image description here

Best Answer

I'd say you should have DATETIME fields on the ticket_expected and ticket_status so that it's possible to see when the status of the ticket changed. This would allow you to track changes from start to end.

I would also recommend that use the same name for columns that are used to join tables. So the ticket table should have a ticket_id column that matches the column of the same name in the ticket_expected table. This is standard practice as it avoids a lot of confusion that can happen when writing and bug tracing queries.

Those two points are my only comment as the general design seems to meet your requirements.

Related Solutions

Database design- diagrams

Short answer : yes , add paymentID (PK) to Payments table.
Explanation. Even without knowing any details about your application, I'm pretty sure payment is quite important concept that has to be treated as a separate entity. Thus, it should have primary key. It's possible payment has a composite candidate key (say, combination of (playerID,paymentTimestamp) is very likely to be unique), but it's much easier to deal with simple primary key rather than composite.

As to following requirements.
Class hierarchy doesn't match 100% table structure. There are many concepts that implemented in different ways (for instance, many-to-many relationships, inheritance). Entity identity is one of them , so your approach looks perfectly valid to me.

Classifieds Database Design

You need to decide if your categories are hierarchical (one parent) or graphical (multiple parents). For example, are "cell phones" in the category "electronics" as well as "communications"?

What kinds of fields might your categories have? Use Table Inheritance to allow categories that are similar but not identical.

Let's say you go with hierarchical categories. It should look like this:

-- hierarchical relationships usually have a single table with a field with a self-referencing foreign key:
create table category (
  id bigserial primary key,
  name text not null unique,
  parent_id bigint null references category(id) --if I'm null then I am a top-level category
);

create table ad (
  id bigserial primary key,
  subject text not null
  ...
);

-- this pattern is called a junction table:
create table ad_category (
  ad_id bigint references ad(id),
  category_id bigint references category(id),
  primary key (ad_id, category_id)
);

I would suggest Postgres over MySql, since it supports WITH queries, which make hierarchical querying easier.

Regarding images, consider storing them in Amazon S3 / Cloudfront. You would associated them with the ad via the HTML in the ad. For example, in your UI, the user would upload an image, get the URL back, and this would get plugged in to an <img> tag.

Though you could also create a junction table similar to the ad_category table, and remove orphan images from S3 on a schedule:

create table ad_picture (
  ad_id bigint not null references ad(id),
  picture_url text not null,
  unique (ad_id, picture_url)
);

Best Answer

Related Solutions

Database design- diagrams

Classifieds Database Design

Related Question