CODEWITHSUNDEEP
sqldatabaserelational-database
user3860870
user3860870

Reputation: 13

SQL database relations

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.

Upvotes: 1

Views: 107

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562911

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.

Upvotes: 1

ron tornambe
ron tornambe

Reputation: 10780

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

Upvotes: 0

Related Questions