Re: Which database design is better

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: David Browne (meat_at_hotmail.com)
Date: 10/03/04 

Date: Sun, 3 Oct 2004 16:12:14 -0500

"Jacco Schalkwijk" <jacco.please.reply@to.newsgroups.mvps.org.invalid> wrote
in message news:esi4p0VqEHA.952@TK2MSFTNGP10.phx.gbl...
>> But what's wrong with the circular foreign key reference? It faitfully
>> models what's going on here.
>
> Good question, I had to think about that.
>
> If you have the circular foreign key reference, that doesn't guarantee
> that the Employee actually works in the store, to which he/she is assigned
> as the key employee. It only guarantees that the EmployeeID actually
> exists in the Employee table. And you can't have a foreign key on both the
> StoreID and EmployeeID column from the Store table referencing the
> Employee table.
>

First off, it's still better to guarantee at least that the key employee
exists in the employee table,

Second, you can make the relationship include the store number to guarantee
that the key employee belongs to the correct store.

David

alter table store drop constraint fk_store_key_employee
drop table employee
drop table store

go
create table store
(
 id int primary key,
 key_employee int null
)

create table employee
(
  id int not null,
  store int not null references store,
  constraint pk_employee
  primary key(id,store)
)
go

alter table store
add constraint fk_store_key_employee
foreign key (key_employee,id) references employee(id,store)

go
insert into store (id) values (1)
insert into store (id) values (2)
insert into employee (id,store) values (1,1)
insert into employee (id,store) values (2,1)
insert into employee (id,store) values (3,2)
insert into employee (id,store) values (4,2)

update store
set key_employee = 2
where id = 1
--succeeds

update store
set key_employee = 2
where id = 2
--fails

Relevant Pages

  • Re: Which database design is better
    ... > But what's wrong with the circular foreign key reference? ... the Employee actually works in the store, to which he/she is assigned as the ...
    (microsoft.public.sqlserver.programming)
  • Re: Which database design is better
    ... StoreID PK, FK ... Employee table. ... You have an extra table, but you won't have circular foreign key references, ... > Table Store ...
    (microsoft.public.sqlserver.programming)
  • The IC bookstore, revisited
    ... bookstore example is a great one for evolution, ... but his store functions enough to turn a profit and continue existing. ... and pretty soon they decide they'd like a 3rd employee. ... so our patrons can drink some coffee while they read their ...
    (talk.origins)
  • Re: Stuck, Oredering form, Any ideas?
    ... to tie each uniform to a specific employee. ... goes to a single supplier; in other words, if you're ordering x article from ... Supplier (foreign key from tblSuppliers) ... tblOrders, then use it as the foreign key in this table, instead of ...
    (microsoft.public.access.gettingstarted)
  • RE: application for an employment
    ... Putting a box with a public IP on a public net offering ... buy before visiting the store, nor to only buy products that ... probably have employee records in there someplace. ... internet servics. ...
    (Security-Basics)