Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Narren
Starting Member
1 Post |
 Posted - 2013-06-07 : 07:49:47
|
| Hey guys. First time poster here. I'm totally new to SQL and decided I wanted to make a dummy project of a ticketing system: basically I want to have a database somewhere of all the people I've done some work for as a contractor, what their status is, any case notes, etc. I also want it to be extendable - eventually I might end up doing this for someone as part of a project, and they'll have multiple users.I'm totally new to SQL - the most I've done with a database is constructing some queries to pull information from an existing database. I figure learning it can't be that bad of an idea though, so I'm not really interested in an off the shelf solution - I want to figure out how to do this on my own if possible.What I want from the DB interface is something similar to the way a forum post works - there are a series of entries, chronologically ordered, but the series is basically infinite (or very large). So there'll be a ticket ID number with some other info (Ticket 1, Steven Manning, last update 5/11/2013 at 15:27), and then there'll be the various posts ("5/1/2013 14:45 - 'Called client about potential sale, was receptive. Scheduled to follow up on 5/4'5/4/2013 13:31 - 'Followed up with client. They are really interested in product. Agreed to demo on 5/11 at 5 Rockrose lane, Somewhere, TX, 01234.'5/11/2013 15:27 - 'Showed client prototype. They loved it, complained about DB interaction, but want a larger project.'"as an example without any real formatting).Where I'm running into trouble is figuring out how to handle the individual posts - do I create a separate posts table, give each post their "Parent Ticket ID", date, text, user that made the post, and post number field? Should I treat posts like linked lists - no individual post id number but instead just linking them to previous posts until we get to the end of the chain? Can I do things in the Ticket field with a list?I'm trying to figure out what the "Right" way to do this is so if I end up doing this professionally (not the weirdest ask I've gotten, but I don't like having to say "That's not something I'm familiar with."), and the people I develop the system for eventually expand and pick up a DB person, they won't look at the code and go "What was this idiot doing?".Thanks a lot for your help. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-07 : 08:01:42
|
| I think what you need is two tables 1. Master table for tickets may be TicketMaster with TicketID(identity col maybe),Owner,DateCreated,LastModified2. Details table like TicketDetails with DetailID (identity col maybe),TicketID (pointing to master tables TicketID by foreign key relationship), Notes (all your text for a row),DatePostedso you can always link up between the tables using TicketID to get associated ntoes and use DetailID or DatePosted for chronological ordering.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
|
|
|
|
|
Building a Custom Ticketing system