Question About Design And Saving Data

Last Post 26 Jun 2007 09:50 AM by lmf232s. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
lmf232s
New Member
New Member

--
26 Jun 2007 06:18 AM
This is just a general question to see how you deal with this or what your doing to avoid it.

Basically what im looking for is information on what information is saved to a record (The Primary Key or the Data Value).

Lets look at a sales order. Well say that we have 2 tables; sales order header (SO) and sales order line (SO_LINE). If i was going to save a new SO_LINE I would include the SO NUMBER (PK) in the SO_LINE record. This would allow me to look up SO# 9843 and find all the SO_LINE items associated with it. A basic join on the SO_LINE table.

Ok here is the example that im curious about. Say you have a web app and you have a drop down that is populated with employee names from a DB. Lets say there are 20 names total in the table.

Lets say in your web app and your going to add a new record and that record consists of 10 fields with one of those fields being the drop down of 20 employee names.

HERE IS THE QUESTION:
When you write the record to the DB do you: 1) Write the PK of the employee name? 2) Write the text value of the employee name?


This is a problem I face. While the 20 employee names are not constantly changing, from time to time an employee does leave and we have to either remove the employee name or replace the employee name with the new employee.

I ask this because of these following problems.
1) If i write the PK of the employee name to the DB then if that employee leaves they must be removed from the drop down. If that happens then the primary record can not join on the employee name any more to get the employee name because the employee has been removed from the table.

2) If the employee name is changed to the new employees name then the new employee displays for all the old records that the old employee was associated with (Inheriting all the stuff of the old employee).

3) If you save the employee name to the record, then when you go to edit the record and load the screen, when the drop down is loaded its unable to select the correct employee name because that employee does not exist in the DB anymore (in the case of .net this will cause an error because the selected value does not exist, asp is easier to deal with).

These are just a couple of issues and im sure there are many more but I guess these are the basic problems that I face. Its almost like damn if you do damn if you don’t. In on situation you may have 4 joins to get 4 values from lookup tables where as if you just would of wrote the value to the record then you would of just had to do 1 select but then I guess this defeats the purpose of having a relational DB.

Im open ears!
lmf232s
New Member
New Member

--
26 Jun 2007 09:50 AM
Ya that makes sense. I never like to delete stuff because of the this type of problem but thought I would ask the more experienced users.

I belive for the most part I have been doing exaclty what you mentioned but I think I need to come up with a better process for this kind of thing.

Yes I did get your email the other day. Sounds like youve been pretty busy lately and I know exaclty what you mean about spending 9-12 hrs in front of the computer and then you cant wait to get away from it and do something else. Run into that problem all the time

GoldenGal
New Member
New Member

--
19 Jul 2007 10:57 AM
You've asked the question "When you write the record to the DB do you: 1) Write the PK of the employee name? 2) Write the text value of the employee name? "...

...but the answer, based on the conditions that you're describing further down (employees being deleted from the emp table and joins no longer working because the data's missing, modifying history, etc.), is that you should refactor your Employee table.

When an employee leaves the company, you shouldn't be able to delete that employee's records. There are a lot of business and compliance reasons why you have to retain information about the employee (the IRS says so, for starters!).

So, instead of deleting an employee from the table, why not just create a new column, call it DateTerminated, make it nullable, default to NULL. Depending on how you're using the drop-down pick list, you might have to adjust the query to filter and show only those employees where DateTerminated IS NULL -- if you want to show a list of current employees, that's what you'd do. Nevertheless, your joins won't fail, because whether or not an employee is still with the company, their record will still be in the Employee table. Neither will history change -- no more new employees "inheriting" events from past employees.

You are not authorized to post a reply.

Acceptable Use Policy