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
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
Id, IssueTypeId, CreatedBy, CreateDate, Status, Owner
Id, Name
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.