Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Protocol for PKs (Primary Keys) with Null Entries?

  1. Dec 20, 2015 #1

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Hi all :I have the table Emp (EmpNum PK, EmpLastNM, EmpEducation, EmpDependent, EmpEducation,...EMpHireDate) which I want to normalize and then I want to do an ERD for it.

    I must break this into tables one for which , it seems, I cannot have a non-null PK:

    Now EmpDependent is both multi-valued and has some nulls. My question is, what can I do about setting up a PK for empdependent given the null?
    I have an idea of using the triple (Emp_Num, Emp_DepName, Emp_DepRelation) as the PK; an employee may have more than one dependent by the same name, but (given dependents are nuclear family) , not likely two dependents with both same name and relation.

    STILL, there is an employee without dependents, meaning this triple will contain some nulls. How does one usually deal with this?
     
  2. jcsd
  3. Dec 21, 2015 #2

    PAllen

    User Avatar
    Science Advisor
    Gold Member

    You wouldn't enter rows in this table for employees with no dependents. There is no point in doing so; then your PK is valid.
     
  4. Dec 21, 2015 #3

    Samy_A

    User Avatar
    Science Advisor
    Homework Helper

    Employee John Smith has a son Peter Smith. He divorces, gets full custody of Peter, and marries again with Carole. Alas, Carole was married before to Brian Smith, and they also had a son Peter Smith, for whom Carole has full custody. Now the nuclear family of John and Carole has two Peter Smith's in it.

    Quite unlikely, sure, but I wouldn't structure a database with a PK that could conceivably become non-unique.
    Why not add a PK (autonumber) to the EmpDependent table?

    Another potential problem with the structure are two employees married to each other. Are their children dependents of both? Maybe you need three tables: employees, dependents, employees_dependents_relation.
     
    Last edited: Dec 21, 2015
  5. Dec 21, 2015 #4

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Thanks, that is precisely the key I had in mind: {emp_num, emp_dependentname, emp_relation}. But you're right to consider guarding against very common names: john smith, bob jones, etc. Still, the may be a way around it: we can use Pete Smith for one, Peter Smith for the other and so on. This I think can be made to work for most English names , who have informal as well as formal names :Charles, Charlie, Chuck, Pete, Peter, Bob, Robert. I think something similar may be the case for names in other languages. A problem from this may be the one may want to enter the person's actual legal name. And you don't want to introduce the SSN, since you want to keep it private. But I think the issue of wanting the person's legal name may not really matter that much, as this is kind of secondary and could be obtained in a different way.
     
  6. Dec 21, 2015 #5

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    I wonder if using the employee's name itself as a dependent would also work for these cases, understood (maybe written some way in the data dictionary) to mean the employee has no dependents. But this may slow the search and take up a lot of memory . So maybe your advice is best and we could rename the table: EmployeesWithDependents to make your point clear.
     
  7. Dec 21, 2015 #6

    Samy_A

    User Avatar
    Science Advisor
    Homework Helper

    I think we are talking about different things here.
    The combination of fields {emp_num, emp_dependentname, emp_relation} as primary key doesn't look like a good idea. Once you have to start with tricks (enter Charles as Charlie if need be, ...) it will get messy sooner rather than later.
    If we ignore the problem of dependents of two employees I mentioned at the end of my previous post, why not simply add a numeric primary key to your EmpDependent table? That way you don't have to do any tricks with common names: every dependent will have his own key.

    I don't understand this. I thought the second table was a table with dependents. The name EmployeesWithDependents suggests that this is a table of Employees. @PAllen advice is correct: employees with no dependent simply will have no related records in the EmpDependent table. No need for any special infrastucture. If you need a list of employees without dependents, an outer join between the two tables, with a NULL criterium on the EmpDependent PK will provide that.
     
  8. Dec 21, 2015 #7

    Borg

    User Avatar
    Science Advisor
    Gold Member

    Simply put, this is all that you need to do.
     
  9. Dec 21, 2015 #8

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    OK, thanks to all; as it must be clear to you, I am pretty new at this, so I lack a context in which to make reasonable/sound decisions at this point.
     
  10. Dec 23, 2015 #9
    Just picked up on this and in relation to your other recent posts I have a couple of questions:
    how are you learning this stuff - are you following a course or book?
    what is your aim and what tools are you using - SqlServer is great if you are developing an enterprise application and have access to all the tools in a full .NET development environment, but if you just want to learn about RDBMSs there may be better ways

    As a guide, your learning should lead you to designing a schema something like this:
    • There is an employee entity so we need an employees table
    • Whilst it looks like we could use EmployeeNumber as the Primary Key for this table there are advantages to having a key that is independent of any property value so we use an auto-increment key which is by convention called id.
    • An Employee can have more than one Dependent, so we need a dependents table to represent this entity
    • Again the Dependents table has an auto-increment key called id.
    • The Dependents table has a column fk_employee which creates a many-to-one relationship to the Employees table.
     
  11. Dec 23, 2015 #10

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    I am taking a class actually. We have done some setting up of ERDs given different types of data tables. Schools did not provide us with software, only class was based on MSSQL2014 , so I used the free version offered by minisoft (which I had trouble installing, BTW).

    So I am trying to learn how to work with it, but I also find it interesting and I want to learn it for its own sake.
     
    Last edited: Dec 23, 2015
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: Protocol for PKs (Primary Keys) with Null Entries?
  1. NULL undeclared (Replies: 1)

Loading...