I have a PERSON table which has a PERSON_ID as its primary key. It contains one entry per person.
I also have STUDENT table and EMPLOYEE table which inherits PERSON table, using PERSON_ID as their primary key and contain detail information specific to students and employee.
My question is how I can find detail information of student or employee, for a given PERSON_ID? As for given PERSON_ID, we don’t know what the type of this person is, and I don’t want to do something like:
get from STUDENT table
if Not found then
get from EMPLOYEE table
Putting a PERSON_TYPE field into PERSON table seems to help a little bit, but I wonder if there is a better solution.
My question is how I can find detail information of student or employee, for a given PERSON_ID?
Well, if you only know that you are looking for a person, shouldn't you only have access to the fields for a person? If you are searching for detail information, then you should already know where to look for those details - ie. you should know whether you are looking for a student or an employee. This is just how I would structure my application...
If it is absolutely necessary that the program automatically figure out what type of person a person is, without using joins on each table, AND a person can only be one type of person, then I would (reluctantly) add the "type" field.
Originally Posted by andrewst
Depending on your DBMS and requirements, it may also make sense to combine all three tables into one, with the student-related columns NULL for an employee and vice versa.
Isn't this totally against normalization? Requirements change. What is going to happen when the application wants to support Teachers and Administrators? You're going to have to remodify your table each time...You want to keep irrelevant information out of your tables, so I wouldn't recommend storing all information in one table.
Before you go ahead and decide though, make sure that you do need to get employee/student information from a person id...IMHO, that seems kind of backwards...but I can see how it could be necessary.
Yes, in the sense that NULLs are totally against normalisation (non-1NF) in a true RDBMS. But in SQL databases NULLs are quite, er, normal. It certainly doesn't break the rules of normalisation in other ways, such as functional dependency. The person_name depends upon the person_id; the employee_salary depends upon the person_id; the student_whatever depends upon the person_id.
Originally Posted by jfulton
Requirements change. What is going to happen when the application wants to support Teachers and Administrators? You're going to have to remodify your table each time...You want to keep irrelevant information out of your tables, so I wouldn't recommend storing all information in one table.
I only said it may make sense. Of course "it depends". But storing subtype data in the same table as the supertype is one valid possibility - quite commonly done. One weighs up the pros and cons. In any case, what's so difficult about modifying a table?
The person_name depends upon the person_id; the employee_salary depends upon the person_id; the student_whatever depends upon the person_id.
Well, the employee_salary depends upon the person_id if that person is an employee. So, does (should) the employee_salary depend upon the person_id? Not really...
Originally Posted by andrewst
In any case, what's so difficult about modifying a table?
Not that it's terribly difficult, I just wouldn't want to be the one who needs to restructure database tables and rewrite existing code. I'd rather add tables and write fresh code without potentially breaking the current program. Everything should function as independently as possible.
But you're right, the best design depends upon the situation.
Thank you for your reply, it has been very helpful.
Actually, the PERSON and STUDET_EMPLOYEE problem I asked here is not the real situation I am facing. I used it as an example as I thought it’s similar enough to my real problem and it is so common that I don’t have to explain it too much. But I just realized that it might be different from the real one, so I think I’d better to put it out, so that I can get better suggestion from you guys.
My real problem is:
There are some existing database tables in my system, like TIME_SHEET, EXPENSE_SHEET, EMPLOYEE…. There is not too much in common within these tables except that we going to make use of Windows Workflow Foundation to handle the processing of the records in these table (something like how an expense sheet get approved and finally post to GL transaction).
We decided to abstract these records as “document”, that is all the records can be processed by WWF are documents and WWF knows how to handle these document.
To achieve this, we created a DOCUMENT table on top of all the tables are going to be used by workflow, and it holds just a few columns only relate to workflow (like WORKFLOW_ID, WORKFLOW_STATE…)
DOCUMENT has primary key DOCUMENT_ID which is GUID, and it’s going to be used as primary key in all the other tables, that is the primary key in TIME_SHEET, EXPENSE_SHEET…will be replaced by this DOCUMEN_ID.
I think I am happy with adding a DOCUMENT_TYPE in my DOCUMENT table for finding document details for given DOCUMENT_ID, but I am really doubt if the whole design is right.
I'm not terribly familiar with the Windows Workflow Foundation, though, so I'm not sure what would be best in your situation...
The only thing that may be of concern is using the GUID as the primary key across tables. I mean it is obviously a legitimate key and makes sense, but performance-wise, I'm not sure how MSSQL will optimize it (or if it will even affect performance).
Hopefully somebody else will be able to help you out more. Or maybe you'd want to try posting the more specific question in one of the MS forums.