Database Design Question

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

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.

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.

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

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