Database Design Question

[TheFearful]

Hey everyone, I have a question about a database design that I have right now. The template I was given from someone is the first attached that you will see. I looked at it and figured that the database design of that is not the best, so I decided to normalize it. I am stuck however when it comes to the whole Checklist ID and Ticket Number. The second picture is my design. Could someone help give me insight on a better design if there is one? I don't want to sacrifice speed for a slightly better design since the more normalized I make it, the more queries I'll have to do in the web application part of this.

[radow]

Hi. I nothing understand from your schems. Can you to write more details, that you will want to do.

[hielo]

Based on what you posted, theTicket# column on the light-blue table (to the right of the red table) has unique values. If this is true, then merge Ticket# with the red table and on the purple table instead of Ticket#, use TicketID.

                   TicketCheckList
                   ===============
              +->* TicketID FK
              |    CheckListID FK *<-----------+
              |                                |
              |                                |
              |                                |
Ticket(Red)   |                                |   CheckList (Green)
======        |                                |   =========
TicketID PK --+ 1                            1 +-- CheckListID PK
TicketType    |                                |   Description
TicketNumber  |                                |   Notes
              |                                |
              |                                |
              |                                |
              |    UserTicketCheckList(Purple) |
              |    =========================== |
              +->* TicketID FK                 |
                   CheckListID FK *<-----------+
              +->* UserId FK
              |    completedIndicator
              |    Date
              |    Notes
User          |
====          |
UserID PK ----+ 1

On your diagram, I don't know why you have the dark-blue table(below the red one, which I labeled above as TicketCheckList), but the info in that table is also in UserTicketCheckList. So if you don't need it, you can get rid of it.

[TheFearful]

Hey Hielo, I'm sorry that I posted the tables the way I did. TicketID is mean for the type of ticket that it is. So there are 3 ticket types: Dedicated, Cable Modem, and Other.

If I used TicketID for the purple table, then I would only be able to use 1,2, or 3 for each row and the ticket# would never be used. Or am I missing something from your explanation? I'm not the best with DB design so discussions like this help me out a lot.

[hielo]

On my proposed design I basically got rid of the red table (since it appears to be only a lookup table with three records), and instead took the red TicketID column and used it as the TicketType in the light-blue table. The records on the light-blue (Ticket) table would look as follows:

Ticket (On this table TicketID is PK auto_increment)
=====
TicketID    TicketNumber  TicketType 
1                1234567  1
2                2222222  2
3                3333333  3
4                7777777  1
5                8888888  2

Then in the purple table:

TicketID CheckListID status
1              1                   complete
1              3                   
1              4                   
1              5
1              7 
2              2
2              3
2              6
2              8
3              4                   complete
3              7                   complete

To search based on a specific TicketNumber:

SELECT FROM Ticket A INNER JOIN purpletable B on A.TicketID=B.TicketID WHERE A.TicketNumber =2222222

Similarly, to search for specific TicketID

SELECT FROM Ticket A INNER JOIN purpletable B on A.TicketID=B.TicketID WHERE A.TicketID = 2

[TheFearful]

Ah okay. That makes a lot more sense! That also helps a lot!