How should I structure the data?

[doh]

Going to salvage a post made by LBJuice and my reply.

I'm currently building an ASP.NET in C# web application for my job. It will be used to collaborate on company tasks, specifically terminations and new hires. Microsoft SQL will be used to store app data. I currently have 2 tables:

Sessions (Active Sessions):

1. Id
2. EmployeeName
3. Type (As in either termination or new hire)

SessionTasks:

1. Id
2. SessionID (To reference user in Sessions Table)
3. Task
4. Department
5. Status (Complete/Incomplete)

I want to structure the page as such:

Support Services
Terminations:
Nick Doe
Hard Reset Handheld Devices.
Notify Network Infrastructure If User Was Configured For Static IP Address.
Image PC.
Removing Delegates from Outlook.
Ron Doe
Removing Delegates from Outlook.
Remove Jabber Account(s).
etc.
New Hires:
Some Guy
etc
etc
Systems Group

I plan to lay this out in tables not in bullets and I omitted some irrelevant fields as to not blur my question. Which essentially is what is the best way to approach displaying the data this way? Should I structure my tables differently?

In doing research came across a concept of "Parent Rows" and "Child Rows" This concepts require setting up a table with lets say for example Usernames (Similar to my "Sessions" table) and then using the foreign key to reference the "child rows" in another table, THEN use nested foreach loops to print the data.

However, In this situation I don't organize by a distinct column such as names, but rather recurring data departments.
eg: (the table looks like this)

Support Services..........Task
Support Services..........Task
Support Services..........Task
Support Services..........Task
Support Services..........Task
Systems Group............Task
Systems Group............Task
Systems Group............Task
Systems Group............Task
Systems Group............Task


I thought about INNER JOINS which is how I had my data set up initially but that's still problematic when trying to create a parent child relationship with redundant data. This has been racking my brain all weekend.

My reply:

You've got an Employee (employees table) and an EmploymentStatus (employment_statuses table) for each Employee. I would also expect a State model (states table) to handle the State of an employee: in probation, full employee, on leave, terminated, etc.

I would expect employees table to look at least like this:

id
name
created_at (when the record was created)
updated_at (changed when the record changes)


I would expect employment_statuses table to look like:

id
employee_id
state_id
created_at (when the record was created)

I would expect states table to look like:

id
name
state_type (holds the class name of derivatives of the State class)

All of your potential States derrive from the State class.

When you hire an Employee create an Employee object for them and then create an EmploymentStatus for them. If an Employees status changes simply create a new EmploymentStatus for them (do not destroy the old one! It acts as an audit log!).


CRUD will guide you if you let it.