Does this violate normal form?

Person A is a customer
Person A is a vendor
Person A works out of their home
customer and vendor share an address file
Person A has a B (business) type record on address table
Person A has a H (home) type record on address table that
matches the business record (except for type)
Does this violate normal form?"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:%23OXa9phvFHA.1988@.TK2MSFTNGP10.phx.gbl...
> Person A is a customer
> Person A is a vendor
> Person A works out of their home
> customer and vendor share an address file
> Person A has a B (business) type record on address table
> Person A has a H (home) type record on address table that
> matches the business record (except for type)
>
> Does this violate normal form?
>
That's just some english: normal forms don't enter into it.
David|||Since you've told us nothing about keys or functional dependencies how can
we tell?
David Portas
SQL Server MVP
--|||On Tue, 20 Sep 2005 15:35:39 -0400, "DazedAndConfused"
<AceMagoo61@.yahoo.com> wrote:
>Person A is a customer
>Person A is a vendor
>Person A works out of their home
>customer and vendor share an address file
>Person A has a B (business) type record on address table
>Person A has a H (home) type record on address table that
>matches the business record (except for type)
>
>Does this violate normal form?
Well, if what you're saying is that you have:
MyID FKID Type Address
-- -- -- ---
1 123 B 100 Main Street
2 123 H 100 Main Street
With a duplicate address entered twice, each with different type, then
I'm not sure that anything has been violated, it would seem a semantic
issue.
Josh
ps - no the MyId field is not needed, but y'know how it is these days.|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files.
\|||This is not a debug issue, it is a learning experiance, in an earlier post
you indicated using the IDENTITY column wasn't such a good thing.
Not really sure what you mean. Right now I use IDENTITY on the person table
to generate the personID, personID is then assigned to child rows in the
users, customers, employees, phone, email, address tables.
Should I be generating custom assignments instead of using IDENTITY? If I
sometimes sound like I'm from the 1950's it is because I am trying to switch
from Mainframe/VSAM/CICS to .NET. Now is the time to learn the right way.
How bad is it?
-- User Table
CREATE TABLE users
( personID int not null,
userID varchar(10) not null,
pw varchar(10) not null,
type char(1) not null,
sec_lev char(1) not null,
effective smalldatetime not null,
lastupdate smalldatetime not null,
updateby varchar(10) not null,
CONSTRAINT PK_usersID PRIMARY KEY CLUSTERED(userID),
CONSTRAINT FK_updateby FOREIGN KEY (updateby) REFERENCES users (userID),
CONSTRAINT UserMustBeUnique UNIQUE (userid)
)
go
-- Person Table
CREATE TABLE person
( personID int IDENTITY (1,1) not null ,
prefix nvarchar(6) null,
firstname varchar(15) not null,
MI char(1) null,
lastname varchar(20) not null,
suffix nvarchar(6) null,
notes varchar(1000) null,
lastupdate smalldatetime not null,
updateby varchar(10) not null,
CONSTRAINT PK_personID PRIMARY KEY CLUSTERED(personID),
CONSTRAINT FK_pers_prefix FOREIGN KEY (prefix) REFERENCES prefix(prefix),
CONSTRAINT FK_pers_suffix FOREIGN KEY (suffix) REFERENCES suffix(suffix),
CONSTRAINT FK_pers_updateby FOREIGN KEY (updateby) REFERENCES users
(userID)
)
go
CREATE UNIQUE INDEX person_name_ind
ON person (prefix, firstname, lastname, MI, suffix)
go
ALTER TABLE users
ADD CONSTRAINT FK_users_personID FOREIGN KEY (personID) REFERENCES
person(personID)
go
-- Insert into person table
ALTER TABLE person
NOCHECK CONSTRAINT FK_pers_updateby
go
DECLARE @.userID varchar(10)
SET @.userID = 'master'
DECLARE @.myDate smalldatetime
SET @.myDate = getdate()
INSERT INTO person (firstname, lastname, suffix, lastupdate, updateby)
VALUES ('Master', 'Blaster', 'Sr.', @.myDate, @.userID)
go
DECLARE @.userID varchar(10)
SET @.userID = 'testuser'
DECLARE @.myDate smalldatetime
SET @.myDate = DATEADD(mi, 1, getdate())
INSERT INTO person (prefix, firstname, lastname, suffix, lastupdate,
updateby)
VALUES ('Mr.', 'Test', 'User', 'Sr.', @.myDate, @.userID)
go
SELECT * FROM person
DECLARE @.date smalldatetime
set @.date = DATEADD(mi, 3, getdate())
INSERT INTO person (prefix,firstname,lastname,suffix,lastup
date,updateby)
VALUES ('Mrs.','Test','User','Sr.', @.date,'mtestuser')
go
ALTER TABLE person
CHECK CONSTRAINT FK_pers_updateby
go
SELECT * FROM person
go
-- Insert records into users table
DECLARE @.userID varchar(10)
SET @.userID = 'master'
DECLARE @.personID int
SET @.personID = (SELECT personID FROM person WHERE updateby = @.userID)
DECLARE @.myDate smalldatetime
SET @.myDate = (SELECT lastupdate FROM person WHERE personID = @.personID)
INSERT INTO users (personID, userID, pw, type, sec_lev, effective,
lastupdate, updateby)
VALUES (@.personID, @.userID, 'masterpw', UPPER('e'), 'A', @.myDate, @.myDate,
@.userID)
go
DECLARE @.userID varchar(10)
SET @.userID = 'testuser'
DECLARE @.personID int
SET @.personID = (SELECT personID FROM person WHERE updateby = @.userID)
DECLARE @.myDate smalldatetime
SET @.myDate = (SELECT lastupdate FROM person WHERE personID = @.personID)
INSERT INTO users (personID, userID, pw, type, sec_lev, effective,
lastupdate, updateby)
VALUES (@.personID, @.userID, 'testpw', UPPER('c'), 'C', @.myDate, @.myDate,
@.userID)
go
SELECT * FROM person
SELECT * FROM users
go
-- Customer Table
CREATE TABLE customer
( personID int not null,
userID varchar(10) null,
since smalldatetime not null,
notes varchar(1000) null,
lastupdate smalldatetime not null,
updateby varchar(10) not null,
CONSTRAINT PK_cust_personID PRIMARY KEY CLUSTERED(personID),
CONSTRAINT FK_cust_personID FOREIGN KEY (personID) REFERENCES
person(personID),
CONSTRAINT FK_cust_updateby FOREIGN KEY (updateby) REFERENCES
users(userID)
)
go
CREATE INDEX cust_userID_ind
ON customer (userID)
go
-- Insert records into customer table
DECLARE @.personID int
SET @.personID =
(SELECT personID FROM person
WHERE (prefix = 'Mr.' AND firstname = 'Test' And lastname = 'User')
)
DECLARE @.myDate smalldatetime
SET @.myDate =
(SELECT lastupdate FROM person
WHERE personID = @.personID)
DECLARE @.userID varchar(10)
SET @.userID =
(SELECT updateby FROM person
WHERE personID = @.personID)
INSERT INTO customer (personID, userID, since, lastupdate, updateby)
VALUES (@.personID, @.userID, @.myDate, @.myDate, @.userID)
go
SELECT * FROM users
SELECT * FROM person
SELECT * FROM customer
go
-- Employee Table
CREATE TABLE employee
( personID int not null,
empssi char(11) not null,
dob smalldatetime null,
license varchar(15) null,
licstate char(2) null,
userID varchar(10) null,
region char(3) null,
dept char(3) null,
position char(3) null,
postype char(1) null,
hiredate smalldatetime not null,
termdate smalldatetime null,
salary money not null,
freq char(1) not null,
notes varchar(1) null,
lastupdate smalldatetime not null,
updateby varchar(10) not null,
-- Primary Key id is assigned from person table personID
CONSTRAINT PK_emp_personID PRIMARY KEY CLUSTERED(personID),
-- Driver licence state must be in states table
CONSTRAINT FK_lic_state FOREIGN KEY (licstate) REFERENCES
states(state_abrv),
-- Employee personID PRIMARY field must have valid person.personID
CONSTRAINT FK_emp_personID FOREIGN KEY (personID) REFERENCES
person(personID),
-- updateby field must be valid userID
CONSTRAINT FK_emp_updateby FOREIGN KEY (updateby) REFERENCES
users(userID)
)
go
-- Employee SSI number index, index must be unique
CREATE UNIQUE INDEX empssi_ind
ON employee (empssi)
go
-- Employee userID index
CREATE INDEX emp_userID_ind
ON employee (userID)
go
DECLARE @.personID int
SET @.personID =
(SELECT personID FROM person
WHERE (firstname = 'Master' And lastname = 'Blaster')
)
DECLARE @.myDate smalldatetime
SET @.myDate = (SELECT lastupdate FROM person WHERE personID = @.personID)
DECLARE @.userID varchar(10)
SET @.userID = (SELECT updateby FROM person WHERE personID = @.personID)
INSERT INTO employee (personID, empssi, dob, license, licstate, userID,
region, dept, position, postype, hiredate, salary, freq,
lastupdate, updateby)
VALUES (@.personID, '003-91-9823', '6/12/1960', '06BRM60121', 'NH', @.userID,
'C', 'IT', 'DBA', 'S', '10/18/1993', $78000, 'B', @.myDate, @.userID)
go
SELECT * FROM users
SELECT * FROM person
SELECT * FROM customer
SELECT * FROM employee
go
-- Create communication preferance table
-- type H=Home, B=Business,C=Cell, etc.
-- preference P=Phone, E=Email, M=Mail
CREATE TABLE commPref
( personID int not null,
type char(1) not null,
preference char(1) not null,
notes varchar(1000) null,
lastupdate smalldatetime not null,
updateby varchar(10) not null,
CONSTRAINT PK_CP_personID PRIMARY KEY CLUSTERED(personID),
CONSTRAINT FK_CP_personID FOREIGN KEY (personID) REFERENCES
person(personID),
CONSTRAINT FK_CP_updateby FOREIGN KEY (updateby) REFERENCES users (userID)
)
go
-- Insert records into communication preference table
DECLARE @.personID int
SET @.personID =
(SELECT personID FROM person
WHERE (updateby = 'Testuser')
)
DECLARE @.myDate smalldatetime
SET @.myDate =
(SELECT lastupdate FROM person
WHERE personID = @.personID)
DECLARE @.userID varchar(10)
SET @.userID =
(SELECT updateby FROM person
WHERE personID = @.personID)
INSERT INTO commPref (personID, type, preference, lastupdate, updateby)
VALUES(@.personID, 'H', 'P', @.myDate, @.userID)
go
DECLARE @.personID int
SET @.personID =
(SELECT personID FROM person
WHERE (updateby = 'Master')
)
DECLARE @.myDate smalldatetime
SET @.myDate =
(SELECT lastupdate FROM person
WHERE personID = @.personID)
DECLARE @.userID varchar(10)
SET @.userID =
(SELECT updateby FROM person
WHERE personID = @.personID)
INSERT INTO commPref (personID, type, preference, lastupdate, updateby)
VALUES(@.personID, 'B', 'P', @.myDate, @.userID)
go
SELECT * FROM users
SELECT * FROM person
SELECT * FROM customer
SELECT * FROM employee
SELECT * FROM commPref
go
CREATE TABLE phone
( personID int not null,
type char(1) not null,
seq int not null,
preferred char(1) not null,
phone char(13) not null,
notes varchar(1000) null,
lastupdate smalldatetime not null,
updateby varchar(10) not null,
CONSTRAINT PK_phone UNIQUE NONCLUSTERED(personID, type, seq),
CONSTRAINT FK_ph_personID FOREIGN KEY (personID) REFERENCES
person(personID),
CONSTRAINT FK_ph_updateby FOREIGN KEY (updateby) REFERENCES users(userID),
CONSTRAINT invPhoneType CHECK (type LIKE '[HBC]'),
CONSTRAINT invPhonePref CHECK (preferred LIKE '[YN]'),
CONSTRAINT invPhonePattern CHECK (phone LIKE
'([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),
)
go
-- Phone personID index
CREATE CLUSTERED INDEX ph_personID_ind
ON phone (personID)
go
-- Phone index
CREATE INDEX phone_ind
ON phone (phone)
go
-- Insert records into phone table
DECLARE @.personID int, @.myDate smalldatetime, @.userID as varchar(10), @.seq
int
SET @.personID =
(SELECT personID FROM person
WHERE (updateby = 'Master')
)
SET @.myDate =
(SELECT lastupdate
FROM person
WHERE personID = @.personID)
SET @.userID =
(SELECT updateby
FROM person
WHERE personID = @.personID)
SET @.seq =
(SELECT max(seq) FROM phone
WHERE (personID = @.personID AND type = 'B')
)
IF @.seq is null
SET @.seq = 1
ELSE
SET @.seq = @.seq + 1
INSERT INTO phone (personID, type, seq, preferred, phone, lastupdate,
updateby)
VALUES (@.personID, 'B', @.seq, 'Y', '(555)555-1212', @.myDate, @.userID)
go
DECLARE @.personID int, @.myDate smalldatetime, @.userID as varchar(10), @.seq
int
SET @.personID =
(SELECT personID FROM person
WHERE (updateby = 'Master')
)
SET @.myDate = DATEADD(mi, 1, getdate())
SET @.userID =
(SELECT updateby
FROM person
WHERE personID = @.personID)
SET @.seq =
(SELECT max(seq) FROM phone
WHERE (personID = @.personID AND type = 'H')
)
IF @.seq is null
SET @.seq = 1
ELSE
SET @.seq = @.seq + 1
INSERT INTO phone (personID, type, seq, preferred, phone, lastupdate,
updateby)
VALUES (@.personID, 'H', @.seq, 'N', '(555)555-1112', @.myDate, @.userID)
go
DECLARE @.personID int, @.myDate smalldatetime, @.userID as varchar(10), @.seq
int
SET @.personID =
(SELECT personID FROM person
WHERE (updateby = 'Master')
)
SET @.myDate = DATEADD(mi, 2, getdate())
SET @.userID =
(SELECT updateby
FROM person
WHERE personID = @.personID)
SET @.seq =
(SELECT max(seq) FROM phone
WHERE (personID = @.personID AND type = 'C')
)
IF @.seq is null
SET @.seq = 1
ELSE
SET @.seq = @.seq + 1
INSERT INTO phone (personID, type, seq, preferred, phone, lastupdate,
updateby)
VALUES (@.personID, 'C', @.seq, 'N', '(555)555-1122', @.myDate, @.userID)
go
DECLARE @.personID int, @.myDate smalldatetime, @.userID as varchar(10), @.seq
int
SET @.personID =
(SELECT personID FROM person
WHERE (updateby = 'Master')
)
SET @.myDate = DATEADD(mi, 3, getdate())
SET @.userID =
(SELECT updateby
FROM person
WHERE personID = @.personID)
SET @.seq =
(SELECT max(seq) FROM phone
WHERE (personID = @.personID AND type = 'C')
)
IF @.seq is null
SET @.seq = 1
ELSE
SET @.seq = @.seq + 1
INSERT INTO phone (personID, type, seq, preferred, phone, lastupdate,
updateby)
VALUES (@.personID, 'C', @.seq, 'N', '(555)555-1222', @.myDate, @.userID)
go
DECLARE @.personID int, @.myDate smalldatetime, @.userID as varchar(10), @.seq
int
SET @.personID =
(SELECT personID FROM person
WHERE (updateby = 'Testuser')
)
SET @.myDate =
(SELECT lastupdate
FROM person
WHERE personID = @.personID)
SET @.userID =
(SELECT updateby
FROM person
WHERE personID = @.personID)
SET @.seq =
(SELECT max(seq) FROM phone
WHERE (personID = @.personID AND type = 'H')
)
IF @.seq is null
SET @.seq = 1
ELSE
SET @.seq = @.seq + 1
INSERT INTO phone (personID, type, seq, preferred, phone, lastupdate,
updateby)
VALUES (@.personID, 'H', @.seq, 'Y', '(555)555-2121', @.myDate, @.userID)
go
SELECT * FROM users
SELECT * FROM person
SELECT * FROM customer
SELECT * FROM employee
SELECT * FROM commPref
SELECT * FROM phone
go
CREATE TABLE email
( personID int not null,
type char(1) not null,
seq int not null,
preferred char(1) not null,
e_mail varchar(35) not null,
notes varchar(1000) null,
lastupdate smalldatetime not null,
updateby varchar(10) not null,
CONSTRAINT PK_email UNIQUE NONCLUSTERED (personID, type, seq),
CONSTRAINT FK_email_personID FOREIGN KEY (personID) REFERENCES
person(personID),
CONSTRAINT FK_email_updateby FOREIGN KEY (updateby) REFERENCES
users(userID),
CONSTRAINT invEmailPattern CHECK (e_mail LIKE '%@.%.%'),
CONSTRAINT invEmailType CHECK (type LIKE '[HB]'),
CONSTRAINT invEmailPref CHECK (preferred LIKE '[YN]')
)
go
-- Email personID index
CREATE CLUSTERED INDEX em_personID_ind
ON email (personID)
go
-- Email index
CREATE INDEX email_ind
ON email (e_mail)
go
-- Insert records into email table
DECLARE @.personID int, @.myDate smalldatetime, @.userID as varchar(10), @.seq
int
SET @.personID =
(SELECT personID FROM person
WHERE (updateby = 'Master')
)
SET @.myDate =
(SELECT lastupdate
FROM person
WHERE personID = @.personID)
SET @.userID =
(SELECT updateby
FROM person
WHERE personID = @.personID)
SET @.seq =
(SELECT max(seq) FROM email
WHERE (personID = @.personID AND type = 'B')
)
IF @.seq is null
SET @.seq = 1
ELSE
SET @.seq = @.seq + 1
INSERT INTO Email (personID, type, seq, preferred, e_mail, lastupdate,
updateby)
VALUES (@.personID, 'B', @.seq, 'Y', 'master@.oo.com', @.myDate, @.userID)
go
DECLARE @.personID int, @.myDate smalldatetime, @.userID as varchar(10), @.seq
int
SET @.personID =
(SELECT personID FROM person
WHERE (updateby = 'Master')
)
SET @.myDate = getdate()
SET @.userID =
(SELECT updateby
FROM person
WHERE personID = @.personID)
SET @.seq =
(SELECT max(seq) FROM email
WHERE (personID = @.personID AND type = 'B')
)
IF @.seq is null
SET @.seq = 1
ELSE
SET @.seq = @.seq + 1
INSERT INTO Email (personID, type, seq, preferred, e_mail, lastupdate,
updateby)
VALUES (@.personID, 'B', @.seq, 'N', 'dba@.oo.com', @.myDate, @.userID)
go
DECLARE @.personID int, @.myDate smalldatetime, @.userID as varchar(10), @.seq
int
SET @.personID =
(SELECT personID FROM person
WHERE (updateby = 'Master')
)
SET @.myDate = DATEADD(mi, 3, getdate())
SET @.userID =
(SELECT updateby
FROM person
WHERE personID = @.personID)
SET @.seq =
(SELECT max(seq) FROM email
WHERE (personID = @.personID AND type = 'H')
)
IF @.seq is null
SET @.seq = 1
ELSE
SET @.seq = @.seq + 1
INSERT INTO Email (personID, type, seq, preferred, e_mail, lastupdate,
updateby)
VALUES (@.personID, 'H', @.seq, 'N', 'mblaster@.oo.com', @.myDate, @.userID)
go
DECLARE @.personID int, @.myDate smalldatetime, @.userID as varchar(10), @.seq
int
SET @.personID =
(SELECT personID FROM person
WHERE (updateby = 'Testuser')
)
SET @.myDate =
(SELECT lastupdate
FROM person
WHERE personID = @.personID)
SET @.userID =
(SELECT updateby
FROM person
WHERE personID = @.personID)
SET @.seq =
(SELECT max(seq) FROM email
WHERE (personID = @.personID AND type = 'H')
)
IF @.seq is null
SET @.seq = 1
ELSE
SET @.seq = @.seq + 1
INSERT INTO Email (personID, type, seq, preferred, e_mail, lastupdate,
updateby)
VALUES (@.personID, 'H', @.seq, 'Y', 'tuser@.oo.com', @.myDate, @.userID)
go
SELECT * FROM users
SELECT * FROM person
SELECT * FROM customer
SELECT * FROM employee
SELECT * FROM commPref
SELECT * FROM phone
SELECT * FROM email
go
CREATE TABLE address
( personID int not null,
type char(1) not null,
address1 varchar(25) not null,
address2 varchar(25) null,
city varchar(20) not null,
state char(2) not null,
zip varchar(5) not null,
zip4 varchar(4) Null,
notes varchar(1000) null,
lastupdate smalldatetime not null,
updateby varchar(10) not null,
CONSTRAINT PK_addr_personID PRIMARY KEY NONCLUSTERED(personID, type),
-- Address personID PRIMARY column must have valid person.personID
CONSTRAINT FK_addr_personID FOREIGN KEY (personID) REFERENCES
person(personID),
CONSTRAINT FK_addr_states FOREIGN KEY (state) REFERENCES
states(state_abrv),
CONSTRAINT FK_addr_updateby FOREIGN KEY (updateby) REFERENCES
users(userID),
CONSTRAINT invAddrType CHECK (type LIKE '[HBAOTV]'),
CONSTRAINT zipMustBeNumeric CHECK (zip LIKE '[0-9][0-9][0-9][0-9][0-9]'),
CONSTRAINT zip4MustBeNumeric CHECK (zip4 LIKE '[0-9][0-9][0-9][0-9]')
)
go
-- Address personID index
CREATE CLUSTERED INDEX addr_personID_ind
ON address (personID)
go
-- Address personID index
CREATE INDEX addr_address_ind
ON address (address1, zip)
go
-- Insert records into address table
DECLARE @.personID int, @.myDate smalldatetime, @.userID as varchar(10)
SET @.personID =
(SELECT personID FROM person
WHERE (updateby = 'Testuser')
)
SET @.myDate =
(SELECT lastupdate
FROM person
WHERE personID = @.personID)
SET @.userID =
(SELECT updateby
FROM person
WHERE personID = @.personID)
INSERT INTO address (personID, type, address1, city, state, zip, lastupdate,
updateby)
VALUES (@.personID, 'H', '14 Testers HWY.', 'Testville', 'RI', '99999',
@.myDate, @.userID)
go
DECLARE @.personID int, @.myDate smalldatetime, @.userID as varchar(10)
SET @.personID =
(SELECT personID FROM person
WHERE (updateby = 'Master')
)
SET @.myDate =
(SELECT lastupdate
FROM person
WHERE personID = @.personID)
SET @.userID =
(SELECT updateby
FROM person
WHERE personID = @.personID)
INSERT INTO address (personID, type, address1, city, state, zip, lastupdate,
updateby)
VALUES (@.personID, 'B', '1 Pizza Rd.', 'Pizzaville', 'RI', '19999',
@.myDate, @.userID)
go
DECLARE @.personID int, @.myDate smalldatetime, @.userID as varchar(10)
SET @.personID =
(SELECT personID FROM person
WHERE (updateby = 'Master')
)
SET @.myDate = getdate()
SET @.userID =
(SELECT updateby
FROM person
WHERE personID = @.personID)
INSERT INTO address (personID, type, address1, city, state, zip, lastupdate,
updateby)
VALUES (@.personID, 'H', '8 DBA Blvd.', 'Dibaville', 'RI', '29999', @.myDate,
@.userID)
go
DECLARE @.personID int, @.myDate smalldatetime, @.userID as varchar(10)
SET @.personID =
(SELECT personID FROM person
WHERE (updateby = 'Master')
)
SET @.myDate = DATEADD(mi, 2, getdate())
SET @.userID =
(SELECT updateby
FROM person
WHERE personID = @.personID)
INSERT INTO address (personID, type, address1, city, state, zip, lastupdate,
updateby)
VALUES (@.personID, 'V', 'Relax Ln.', 'Offville', 'RI', '39999', @.myDate,
@.userID)
go
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127275885.339847.72700@.z14g2000cwz.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files.
> \
>