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.
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!