|
|
Posted by Greg D. Moore \(Strider\) on 10/12/05 03:42
"Tom" <nospam@yahoo.com> wrote in message
news:hiY2f.481$7h7.465@newssvr21.news.prodigy.com...
> I come to the same conclusion. (see the 10/11/2005 1:03pm post)
>
> As I search Google, this thing is getting very confusing. All the posts
> point to the one dead end conclusion -- "It depends".
>
> I need something solid so I don't have to reinvent the wheel (or think
about
> it). The "It depends" thing just doesn't cut it.
>
"It depends" .
Basically I tend to agree with Celko's philosophy that wheer possible the
key should model reality. If your employees already have some sort of
employee number assigned, use that.
That said, even in many of my tables with natural keys I'll drop in an
Identity column as it can be useful for some ad-hoc queries.
>
>
> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
> news:Xns96ECF3C9E8AE7Yazorman@127.0.0.1...
> > Tom (nospam@yahoo.com) writes:
> >> I am always confused about what to use as the key for a table.
> >>
> >> Let's say I have a company-employee table.
> >>
> >> [company]---*[employee]
> >>
> >> My co-worker likes to use an AutoNumber or Counter as the key for the
> >> [employee] table (and everything).
> >> I personally use an actual key set for the [employee] table.
> >>
> >> So, his table will have one (Autonumber or LONG) column as the key.
The
> >> company_id is just another attribute.
> >> Mine, on the other hand, has 2 columns as the key. (i.e.) company_id +
> >> employee_id
> >
> > I would say that the two alternatives describes two different things.
> > In the one-column case, an employee is rather a person, and if he
> > moves to another company all data that is tied to him are brought with
> > him.
> >
> > In the two-column case, you get a new row when he gets a new job.
> >
> > The latter appears more useful to me. If we want to know who much sales
> > did everyone in company X generate last year, we can't tell with the
> > first setup, since we don't know who worked where that year.
> >
> > If want to know how much aales Bob has generated over the years in
> > different company, maybe there should be a person table, to which
> > employees have a reference.
> >
> >
> > --
> > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> >
> > Books Online for SQL Server SP3 at
> > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
> >
>
>
Navigation:
[Reply to this message]
|