Training Matrix Database

[davegri]

Hi Davegri


This is a great DB for what i need. Am I ok to change things around on this or is this just for viewing? Thought it best to ask first.

Well, my sample databases are for whatever use you want. I provide examples that I hope can be successfully altered by the viewer as seen fit.
If you get stuck with anything, PM me and we can work it out.

[madpiet]

I did something like this a long time ago. If you understand database design reasonably well, it's not that hard. When I did it, I needed to keep track of who had taken what training and when, because the training "certifications" expired after a period of time. I think the hardest question was "what courses are missing?" and that's just a cross join and then an outer join. I would suggest adding minimal records to your tables while testing just to make sure your structure is correct, and then write queries to get the answers you want. once you're sure that works, go on to the UI etc. but a good structure will save you a ton of hassle later on. (been there, done that!). Post your design here if you want and someone here can help you if you get stuck.

[hinchi1]

Thanks for this. I understand

[hinchi1]

I did something like this a long time ago. If you understand database design reasonably well, it's not that hard. When I did it, I needed to keep track of who had taken what training and when, because the training "certifications" expired after a period of time. I think the hardest question was "what courses are missing?" and that's just a cross join and then an outer join. I would suggest adding minimal records to your tables while testing just to make sure your structure is correct, and then write queries to get the answers you want. once you're sure that works, go on to the UI etc. but a good structure will save you a ton of hassle later on. (been there, done that!). Post your design here if you want and someone here can help you if you get stuck.

I think one of the problems I had work my last database was understanding table structure and join types. I don't suppose you have a sample table structure to illustrate what you have said.

[madpiet]

I think one of the problems I had work my last database was understanding table structure and join types. I don't suppose you have a sample table structure to illustrate what you have said.

When I have a crazy database with lots of tables or a set of relationships I don't totally understand, I get a huge whiteboard and literally draw the thing. Something almost silly like
[Customer]---[invoiceHeader]---[InvoiceDetail]---[Product] and then I list all the relevant columns under each.

I think part of your problem may be that the whole Students and Classes database is a great way to see if you really understand correct database design. ("correct" in that you can get answers to your questions from the data using just SQL). So i would start there. Once you've done database stuff for long enough, you can read the ERD and figure out if it can answer a question pretty easily. (Well, for 90% of the questions)

Oh, and the join type stuff... there are *relationships* (Primary key, foreign key stuff) and then there are joins in a query. "relationship" joins are like INNER JOINs (Yeah, this may be dangerous territory... ) But in a query, you can change the join type between the tables to some kind of outer join.

One place you use them is when you're searching for a condition that doesn't exist. For example, "Show me all students that have not taken a given course". (Student cross join Course) MINUS (transcript) ... which is (StudentKey, CourseKey, Grade...)