Looking for some Feedback on a DB Design

Question

I'm currently working on a ticketing system which allows our users to submit tickets based on their own needs. i.e. if Department "A" is submitting a ticket they can have certain types of problem categories (such as "Supplies" or "Printer") along with details pertaining to the chosen category. I have laid out a partial db design and i was looking for some feedback on it. I've never built a system from the ground up by myself so i'm a little bit nervous.

here's my a draft version of my db design

Issues Table

Id | CreatedBy | CreateDate | Status | Owner | AssignedTo | AssignmentDate | 
-----------------------------------------------------------------------------

EquipmentIssueDetails Table

Id | IssueId | Serial # | Make | Model | ....
---------------------------------------------

SupplyIssueDetails Table

Id | IssueId | SupplyId | ItemId | QTY | UnitOfMeasurement
-------------------------------------------------------------

NetworkIssueDetails Table

Id | IssueId | Supervisor |  Details | 
-------------------------------------------------------------

Notes Table

Id | IssueId | Note | CreatedBy | CreateDate
-------------------------------------------------------------

Thanks in advance

Answer 1

This is a possible solution, but it's not very flexible. If there becomes a new type of issues, you would have to alter the database.

Another solution is putting all those different tables into one table with a key value pair of fields. The downside of that approach is you would have to define the keys in the application and the database has no means of checking if all the information is there.

This is a consideration you would have to make. There can be more solutions, but none that I now can think of.

Answer 2

I'd split your Issues table so issues and assignments are separate. Also, I would add an issue types table and add an IssueTypeId column to issues

Issues

Id, IssueTypeId, CreatedBy, CreateDate, Status, Owner

IssueTypes

Id, Name

Assignments

Id, IssueId, AssignedTo, AssignmentDate, Active

This would allow multiple people being assigned to an issue if needed later. It would also allow to record the history of people being de-assigned from an issue. Issue type entries would be as follows: 1: Equipment, 2: Supply, 3:Network

Having a few different issue types may be ok to manage, but if you have a lot, substituting the "Details" tables with a key/value table approach as suggested by Ikke may be better.

Answer 3

Your design is excellent. A separate table to contain the details of each type of issue is exactly the correct approach.

Do not follow the advice of those who think you should have key/value pairs. Such an approach makes some things easier, but it turns quickly into a nightmare when you try to do other things. You have the power of a full RDBMS at your disposal. Use it!

Each row in each table represents a true proposition about the world. The more the DB design can reflect the important realities that you are trying to track, then the better off you are.

If it turns out in the future that you need to track more details, then add more columns. If it turns out that there are additional issue types, then add more tables. An issue-type table adds nothing.

Following this principle will pay huge dividends when it comes time to create reports or do analysis on this data.

You should consider following OG's advice on allowing multiple assignees for each issue, or tracking the history of assignments. It depends on how the system is to be used, or what will need to be reported. Remember that you can't report on data that wasn't stored.

Answer 4

When designing start thinking of how are you going to use this data. What kind of reports will you need to run, how will people get notified of changes to the issue, how will you choose the people to be assigned to the task (you might need tables to store the people available for work and their skills sets (what kind of tasks can they be assigned to), what kind of attachments will you need to allow and where to store that information (a screen shot can help a lot in resolving an issue). What kind of lookup tables do you need for drop downs on your application? Do you need any kind of data auditing? Do you need certain types of issues to be authorized by a supervisor? Can people be automatically aissgned or do you need to keep track of waht they already have ontheir plate before determining who is available. Will you want to be able to determine the data the task should be completed by in order to determine when things are late? You said that certain depatments can only submit certain kinds of tickets, where is the table to store that data? In other words, you really need to get down into the nitty gritty and do the requirements.

BTW, don't let anyone talk you into a key-value store, this is the absolute worst way to store data in a relational database. You not only will have performance problems but you lose alot of the ability to correctly put limits on the fields (such as whether they are required or not) and you are forced to a poor data type choices for some of the information requiring frequent converstions to the correct type of data to perform functions.