October 6, 2009 at 10:53 am
Here is a table:
CREATE TABLE Employee (
EmployeeID int NOT NULL PRIMARY KEY,
EmployeeName varchar(50) NOT NULL,
IsAuthorizedDriver bit NOT NULL,
DriversLicenseNumber varchar(10) NULL
)
The business rule is that every authorized driver must have a driver's license.
In order to validate, should I have a check constraint on the Employee table?
(IsAuthorizedDriver = 1 AND DriversLicenseNumber IS NOT NULL)
OR (IsAuthorizedDriver = 0)
Or should there be two tables?
CREATE TABLE Employee (
EmployeeID int NOT NULL PRIMARY KEY,
EmployeeName varchar(50) NOT NULL
)
CREATE TABLE EmployeeDriver (
EmployeeID int NOT NULL CONSTRAINT FK_EmployeeDriver REFERENCES Employee (EmployeeID),
DriversLicenseNumber varchar(10) NOT NULL
)
Or something entirely different?
October 6, 2009 at 12:57 pm
Sorry it could sound bit obvious but still, is employee and driver different entities? If not I would keep it in one table and put check constraint.
---------------------------------------------------------------------------------
October 6, 2009 at 2:18 pm
In terms of the business rules, there are only employees. Those who are authorized to drive and those who aren't. But that's essentially my question. Should I create a second entity called Driver (or EmployeeDriver, whatever) ?
The second entity approach seems to be a more "normalized" design but I'm curious if that approach is ever used in a production system.
October 6, 2009 at 2:26 pm
Why have the IsAuthorizedDriver column? Do you have people who do have a driver's license, but who aren't authorized to drive?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 6, 2009 at 2:34 pm
To GSquared:
I think it could theoretically happen. The employee could be authorized to drive and then lose that authorization. I don't know if it would make sense to delete the license number.
The driving business logic is that if an employee is authorized, they must have a driver's license in the system. If they aren't authorized, it doesn't matter...
October 6, 2009 at 3:06 pm
I'm in favor of a single table with a check constraint. I don't see any reason to muddy the waters with any additional tables. And you can get everything you want with that contstraint.
CEWII
October 7, 2009 at 9:03 am
8kb (10/6/2009)
To GSquared:I think it could theoretically happen. The employee could be authorized to drive and then lose that authorization. I don't know if it would make sense to delete the license number.
The driving business logic is that if an employee is authorized, they must have a driver's license in the system. If they aren't authorized, it doesn't matter...
Given that, and the low probability that someone will have more than one driver's license, I'd go with a single table with a constraint.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 7, 2009 at 9:56 am
To GSquared...that makes sense. But what about the 2nd normal form?
"each non-key field is functionally dependent on the entire primary key"
You can't have a driver's license without an employee. But in terms of the business logic, the driver's license is really dependent on the fact of whether or not the employee is licensed to drive. But I can't imagine having a primary key based on EmployeeID and IsAuthorizedDriver.
It seems like this is a 2NF violation (but maybe only in theoretical terms?).
This type of issue has bugged me forever. And I figured the only solution was to split the Employee table into two tables (Employee and EmployeeDriver), with a second table that has a one-to-one relationship to the first (because, as you said, an employee will only have one license).
Are there ever times where you would create two tables with a one-to-one relationship like Employee and Driver? Or am I misunderstanding the normalization concept?
October 7, 2009 at 10:09 am
One-to-one/zero tables are quite common. It's called "vertical partitioning", and it's really useful in a number of circumstances. Some DBAs use it any time a column could possibly be null, but I consider that a bit extreme.
As far as the normalization is concerned, yes, it's a violation of "the key, the whole key, and nothing but the key". From a purist point of view, it should be in a separate table because of that. I just don't like building a table that has the sole purpose of storing a bit field and the necessary keys to tie it back to the main table. One of the key principles of relational storage is that you increase storage efficiency by reducing duplicate data. In this case, the duplicate data (the key columns) will take more storage than the bit field will, so duplicating the keys into a sub-table actually defeats the whole idea. It's a case where I'd break the letter of the rules in order to obey the spirit.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply