|
|
Posted by Erland Sommarskog on 10/12/05 00:59
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]
|