SQL database relations

Question

First off, let me just say that I'm still a noob at this real world database stuff, and I was hoping to get some light shed on this situation.

I have recently put together a basic asp web application to manage a SQL database with two tables. The tables are as follows:

Key table:

Key_id Key_desc Key_number Key_location Issued_by Emp_id

Emp table:

Emp_id Emp_fname Emp_lname Emp_phone

As you can see, the tables are linked by the emp_id field. However, the key table allows for null values in the emp_id column in case the key is not assigned to an employee.

Since this is the case, I'm concerned about the overall relationship in the database, and orphan records are becoming a concern.

Since the key table allows for null values for that column, does this mean it is not in 1NF?

As of now, the web application assigns keys by pulling all records from the key table by selecting all records where emp_id is null, and then assigning an emp_id to a selected key.

But the relationship only exists if the key has an associated employee, and it seems like there must be a better way to do this.

It's probably a little late to change the app, so I'm more or less asking this out of sheer curiosity.

I thought I may need an assigned key table and an unassigned key table, but I don't know how I would link them with the current database fields. Can anyone give me some advice on this? Or maybe help me figure out a better way to link the database together?

My main concern is orphan records

This was typed on a mobile, so please forgive me if there are errors.

EDIT: Thanks a ton to those who responded to this. Ive got more help than I thought. Will mark an answer later on, when I figure out which route to take.

Something told me this was the right place to ask.

Mike Sherill : The only relation at the moment is emp_id field being the foreign key in the key table (primary key in the emp_tbl). I couldn't really think of a better way to link them together. — user3860870, Jul 21 '14 at 14:14
@hugh Jones: yes this is actually for a key management system, so that you can find which employees have keys assigned to them, and what doors those keys open. — user3860870, Jul 21 '14 at 14:36
ok, so there is a perfectly sensible scenario where a key is still in the safe and not assigned to anyone? I would say you are on the right track already. — Hugh Jones, Jul 21 '14 at 14:38
@hugh Jones: thanks for easing my mind a bit. Most "textbook" examples that I have worked on didn't have null foreign key values in the primary table, so it threw me for a loop. Now that I know there are some cases where this is acceptable, I feel a bit better. — user3860870, Jul 21 '14 at 15:33

score 1 · Accepted Answer · edited May 23 '17 at 10:33

An alternative is to create a third table, to note the key assignments. If a key is unassigned, just don't put a row in that table. Don't put a emp_id foreign key in the keys table.

Example regarding your question in comments:

CREATE TABLE KeyAssignment (
  key_id INT PRIMARY KEY,
  emp_id INT NOT NULL,
  FOREIGN KEY (key_id) REFERENCES keys (key_id),
  FOREIGN KEY (emp_id) REFERENCES emp (emp_id)
);

No other attributes are needed, because all the details of both keys and employees are already stored in the other tables.

Note the above table isn't exactly a many-to-many table, because key_id alone is the primary key. You can only have one (or zero) rows for each key_id. If you have a row for a given key_id, the emp_id is NOT NULL so an assignment is mandatory. Unassigned keys simply don't have a row in this table (until they become assigned).

As far as whether your original design is in 1NF, yes mostly there is agreement that it is. The value of a non-null Emp_id is a single value from the domain of employees, and that's what's required for 1NF.

SQL has the additional rule that NULL can be considered a virtual member of any domain, although whether this rule agrees with relational theory is controversial. C.J. Date argues that the concept of the NULL in SQL is incompatible with relational theory. But E.F. Codd recognized the importance of a NULL, to signify that the member of a row is inapplicable or unknown.

Thanks for this. I thought about adding an assignment table of some sort, but didn't know what fields I would include into it.. Nor how it would relate to the existing two tables. Could you kindly give me some sort of syntax example of the possible assignment table? Or what fields I could include?.. I'm hesitant to change the design if I don't really have to, but any clarifications on this one would help. — user3860870, Jul 21 '14 at 15:25
@user3860870 Every table (base or query result) holds the rows that make some fill-in-the-blanks statement true. Eg "key [k] is assigned to employee [e]". Find the statements/tables to describe your application situations. NULL is never needed. It complicates statements & queries. But for an SQL DBMS NULL can improve performance and simplify constraints. Normalization theory does not allow NULL. But reasoning about NULL as an extra value for any column allows reasoning about normalization. But it isn't like other values because SQL relation and logic operators treat it differently. — philipxy, Jul 22 '14 at 01:21

ron tornambe · Answer 2 · 2014-07-21T15:33:55.257

0

I think the DB design you are seeking is something like the following (using MySQL syntax):

create table Emp(
  Emp_ID int not null auto_increment PRIMARY KEY,
  LastName varchar(30),
  FirsName varchar(20),
  Phone varchar(20)
);

create table `Key`(
  Key_id int not null auto_increment PRIMARY KEY,
  Description varchar(255),
  Key_number int,
  Key_location int, -- or your data type for this column 
  Issued_by int -- or your data type for this column 
  Emp_id int, -- foreign key to Emp table
  FOREIGN KEY (Emp_id) references Emp(Emp_ID) 
  ON DELETE CASCADE -- or RESTRICT / NO ACTION options -- see notes below  
);

Three additional points:

1) You can also use the RESTRICT / NO ACTION options to prevent the primary key row from being deleted.

2) Avoid using reserved words for table or column names, like Key.

3) I prefer not to prefix column names with the table name, ex. Emp_lname. It is redundant and less readable.

EDIT As per @Hugh Jones, I separated the Issued_by and Emp_id columns - Thanks Hugh

edited Jul 21 '14 at 15:33

answered Jul 21 '14 at 14:39

ron tornambe

10,452
7
33
60

Now that I look more closely, you are right - they are separate fields. I'll edit answer. – ron tornambe Jul 21 '14 at 15:30
@rob tornambe : as far as design goes, this may be what I was looking for. I must mention that I did not originally create the tables, I only built a web app to work with the existing ones. I was thinking of redesigning them, which is where this will come in handy. I will most likely edit the column names to prevent redundancy. Not being familiar with the restrict / no action... Ive got to ask, what would happen if I were to try and delete a key that has been assigned to an employee, from the key table?...would it not delete anything? – user3860870 Jul 21 '14 at 15:49
The key would be deleted and would no longer be associated with that employee - a condition that is likely to occur. If you tried to delete an employee that was assigned one or more keys, the delete would fail and an error issued - assuming the CASCADE RESTRICT was being enforced. – ron tornambe Jul 21 '14 at 17:01

SQL database relations

2 Answers2