Paging database gurus

[Hawkmoon]

If there is anyone here who has some basic skills with either Microsoft Access or Libre Office Base, please contact me by PM. I need to set up a small database. I have both programs and I have used both to do easy, flat file databases. I need to venture into the realm of relational databases, and that has me feeling a bit (okay, more than "a bit") intimidated.

[fifth_column]

I'm not a database guy, but I've heard that relational databases require a real commitment . . . .

[cordex]

I'm not a database guy, but I've heard that relational databases require a real commitment . . . .

Only after you have completed a transaction.

Most of my database experience is MSSQL and MySQL, but the basic foundation is the same.  What are you trying to accomplish?

[AZRedhawk44]

Only after you have completed a transaction.

badump-bump /rimshot

 

[Hawkmoon]

What are you trying to accomplish?

I';m an NRA instructor, certified to teach several different courses. I want to create a small () database to keep track of students, when they took what class(es), etc. I was thinking I would need three tables: Courses (the list of NRA courses); students; and classes (or sessions, meaning not the list of the NRA courses, but the names, dates, and locations for when I give each class). The problem is how to associate students with classes ... and allow for the possibility of a student taking more than one course.

AZRedhawk was kind enough to send me a PM suggesting that I need a fourth table. It makes sense ... I think. But I'm such a novice that I can't grasp how it would work.

[AZRedhawk44]

My proposed solution was four tables.

Courses (Hawkmoon's list of courses he teaches, with per-course related info such as syllabus, prerequisites, required equipment, etc)
Students
Classes (each time a Course is offered it is entered here with a date, location, cost and pertinent info to the session)
StudentClass (many to many junction table... Each class can have a different number of students in it and each student can enroll in a varied number of Classes, this is the cleanest solution.  Additional info stored here could be notes on student behavior, pass/fail or grade, notes on intent to take more courses offered, etc).

Dropping to 3 tables makes it difficult to cleanly log Student-to-Class entries.  It can be done by a series of columns on the Student table (class1, class2, etc) or by a series of entries on the Classes table (student1, student2, student3, etc) or by  adding columns to the Student table to represent each Course offered and entering the ClassID that the student enrolled in (Student would have columns HomeDefense100, PracticalPistol100, etc and the ClassID the student enrolled in would be present) but that doesn't support multiple instances of a student taking the same class.

[AZRedhawk44]

An example DB would be:

Courses (CourseID, CourseDesc, SyllabusLink, RequiredEquipment, Prerequisites)

HG100	Intro to Handguns	C:\MyFolder\HG100.docx	Eye Protection, Ear Protection, Handgun, 100 rounds ammunition	NA
HG200	Defensive Handgun	C:\MyFolder\HG200.docx	Eye Protection, Ear Protection, Handgun, 200 rounds ammunition	HG100

Students (StudentID, LastName, FirstName, Addr1, Addr2, City, State, Zip, Phone, Email)

1	Redhawk	AZ	123 E. Main St	Apt 201	Anytown	AZ	12345	222-555-1234	azredhawk@nunya.biz
2	Redhawk	Wifey	123 E. Main St	Apt 201	Anytown	AZ	12345	222-555-1235	wifeyredhawk@nunya.biz

Classes (ClassID, CourseID, Location, DateFrom, DateTo, Cost)

1	HG100	City Gun Range, 1234 Alder St, Anytown, AZ 12345	2020-01-30	2020-01-31	$50
2	HG100	City Gun Range, 1234 Alder St, Anytown, AZ 12345	2020-02-14	2020-02-15	$50
3	HG100	That Other Gun Range, 2345 Bendy St, Anytown, AZ 12345	2020-03-14	2020-03-15	$50
4	HG200	City Gun Range, 1234 Alder St, Anytown, AZ 12345	2020-04-14	2020-04-15	$100

StudentClass (StudentID, ClassID, Grade, Notes)

1	1	F	Really scary, total noob.  Suggested he re-take the course again next month.
2	1	P	How on earth did she marry this doofus?  She's got to be scared for her life the way he handles himself.  Then again, she's pretty decent with a pistol, unlike her spouse.
1	2	F	May need to find someone else to teach this guy, he's pretty awful.  Unsafe, waving gun all over the place.  Doesn't retain anything you tell him.
1	3	P	Finally doing better.  He at least hit the broad side of the barn.  Now I owe That Other Gun Range repair fees for the holes in the barn though.
1	4	P	Wow, we got a badass here.  Full on gun ninja.  I'm a pretty damn fine instructor, if I do say so myself.
2	4	P	Calm, collected and responsible.

[AZRedhawk44]

I cut a few corners in the example above that I wouldn't do if this were medical records or bookkeeping/accounting and was likely to have professional peers see my work later on.  But it's suitable for what you want to do and you're not likely to run into the situations that those cut-corners are going to create problems, nor likely to have other professional DB Developers look at it with a critical eye.

When you get to a few million rows you might have some problems.  Prolly be a few millenia before you get there though.

[cordex]

AZRedhawk44,
You didn't even tell him to create clustered indices or associate foreign keys!

Hawkmoon,

AZR has given a good framework and basic review of how to build it.  The only thing I might question is the need for a distinct Courses table.  If you were developing a website it would be essential to help automate class creation and so forth, and is definitely the "correct" way to do it, but since you're running this database for your own personal tracking it might be sufficient to put that information into the Classes table at the cost of reentering data each time.  That said, there is nothing wrong with doing it exactly the way AZR has set it up for you.

Next steps would be:
1. Determine exactly what information you need for each table.  Keep in mind the reports you're going to want to see at the end stages.  Make sure you're thinking through which table the information needs to go in.  For example, placing a field like required equipment in the Courses table simplifies creating new Classes, but limits flexibility as all Classes created with that Course will have to share that information, and any change to the value stored in the Course table will impact all historical Classes as well.
2. Determine the correct data type for each record.  DATE, VARCHAR, TEXT, INT, etc.  Don't forget Autonumber for relevant IDs/Primary Keys or defining columns as unique where necessary.
3. Build the database.
4. Build out the forms necessary to insert the data and insert some dummy data.
5. Define your reporting and work out which tables you need to join to get there.
6. Build your reporting using the dummy data to test.

[AZRedhawk44]

AZRedhawk44,
You didn't even tell him to create clustered indices or associate foreign keys!

FK's are the devil.  Like foozball.  :-)  I mostly write ETL's all day.  My insert target tables in my company's product has no FK constraints because the CHECKs on them are a PITA for bulk inserts that temporarily break those logical dependencies (until a later insert corrects the issue).  I grok their logical value, but they do get in the way when speed is your aim and you've got millions of rows to fill in a live environment.

I sent him a script for the 4 tables in question that has PK clustered indexes.  No covering indexes on what are the logical FK columns though.  Won't matter at the size of DB he's going to be dealing with.  The extra couple milliseconds of latency after he's used this for 10 years won't be noticeable.  I even sent him syntax for a view that shows student enrollment and grades.

[cordex]

FK's are the devil.  Like foozball.  :-)  I mostly write ETL's all day.  My insert target tables in my company's product has no FK constraints because the CHECKs on them are a PITA for bulk inserts that temporarily break those logical dependencies (until a later insert corrects the issue).  I grok their logical value, but they do get in the way when speed is your aim and you've got millions of rows to fill in a live environment.

I sent him a script for the 4 tables in question that has PK clustered indexes.  No covering indexes on what are the logical FK columns though.  Won't matter at the size of DB he's going to be dealing with.  The extra couple milliseconds of latency after he's used this for 10 years won't be noticeable.  I even sent him syntax for a view that shows student enrollment and grades.

For his needs he could probably get by as well or better with a handful of Excel spreadsheets and some VLOOKUPs.