|
Home > Archive > microsoft.public.cert.exam.mcsd > November 2002 > Need advice/Help on 70-100 Database Case Studies
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Need advice/Help on 70-100 Database Case Studies
|
|
|
| In conversations with colleagues who have taken 70-100, I have been
cautioned
that in the Database Design Sections, that MS prefers that you use Surrogate
keys in ALL cases, which seems a bit strange.
For example, using Pubs as a case in point,
BookAuthors table,
PK = TitleID, AuthorID
FK = TitleID, AuthorID
My understanding is that this would be graded as WRONG on 70-100. The way
MS wants to see it is:
BookAuthors Table
PK = BookAuthorsKey (Surrogate Identity key)
FK = TitleID, AuthorID
Does anyone have any thoughts/experience about this? I consider the first
item as correct, but on 70-100, it would be marked WRONG...
Further, if in the case study, if a unique data item is described, such as
an AccountNo, that MS would not use that as a primary key, but would define
an independent identity key, e.g., AccountKey as the primary key and
classify the AccountNo field as "other attributes".
Again, if there was a unique identifier in the "real world", such as an
AccountNumber or InvoiceNumber, which would be defined as unique in the case
study, that it should be used as the primary key. My understanding is that
MS would mark this WRONG on 70-100 and would prefer using a db-generated
Primary key.
Any opinions/experience as to the "Microsoft Way" would be greatly
appreciated!!!
| |
|
| "Hank" <don120759@lycos.com> wrote in message
news:ahGA9.27359$Vy4.5730@news4.srv.hcvlny.cv.net...
> In conversations with colleagues who have taken 70-100, I have been
> cautioned
> that in the Database Design Sections, that MS prefers that you use
Surrogate
> keys in ALL cases, which seems a bit strange.
I think your colleagues are correct, MS prefers surrogate keys in all cases.
In the real world, it's debatable. Personally I agree with them in most
cases. I'd never use something 'unique' like an account number or invoice
number as a primary key. Values that are meaningful to the humans inevitably
need to be changed. If it's a pkey value, that can cascade to many other
tables related via foreign key, which is a real pain.
But for many-to-many linking tables, like your example of BookAuthors in
pubs, I have no problem combining the 2 surrogate keys from related tables
into one composite key. Sometimes those tables won't have any other columns
at all, so adding a distinct surrogate key here can add a significant amount
of overhead. And since the composite fields are themselves surrogates, they
should never need changing either. I don't even show those values to users.
However, I think on 70-100 it does seem that MS scores the
all-surrogates-all-the-time style higher than other options. I doubt it's
black and white completely wrong if you deviate, but I've discussed this
with many test takers and there is a consistent impression that a surrogate
for every table gets a better score.
| |
| Erno de Weerd 2002-11-14, 2:23 am |
|
"Tim" <spammers@begone> wrote in message
news:OsXYWf6iCHA.1840@tkmsftngp08...
> But for many-to-many linking tables, like your example of BookAuthors in
> pubs, I have no problem combining the 2 surrogate keys from related tables
> into one composite key. Sometimes those tables won't have any other
columns
> at all, so adding a distinct surrogate key here can add a significant
amount
> of overhead. And since the composite fields are themselves surrogates,
they
> should never need changing either. I don't even show those values to
users.
Well, I disagree with this statement: as soon as these two FK are combined
they ARE meaningful!
And then imagine that you assigned the wrong author to the wrong book and
referred to this composite MEANINGFUL PK...
Erno de Weerd
MCSD MCAD MCT
| |
| Alan Davis 2002-11-14, 2:23 am |
| To some extent, especially given your example, I agree with you. I
think the few points I dropped in the exam were related to what you
are saying. So you will need to use the surrogate key where one is
offered.
However, it is equally as bad to have wide pk's which include columns
which have no meaning to the table. I have seen [bad] db designs
whereby a large hierarchy of relationships resulted in ten or more
columns in a table forming the PK! This is clearly poor design,
including meaningless columns in the table wastes space and slows the
whole rdbms down. Operations performed by the query processor may
determine the PK is useless and not use it in any queries or update
actions (i.e. simply decides to perform a table scan).
Another point in favor of surrogate keys, is so as to avoid the need
to update a PK. It is not ideal for PK's to be changed once created.
The reasons above are often the case why conceptual designs are
physically implemented differently in relational databases. Note that
many of these issues do not arise with other database types such as
networked hierarchical databases (example being idmsx)
On Thu, 14 Nov 2002 05:12:38 GMT, "Hank" <don120759@lycos.com> wrote:
>In conversations with colleagues who have taken 70-100, I have been
>cautioned
>that in the Database Design Sections, that MS prefers that you use Surrogate
>keys in ALL cases, which seems a bit strange.
>
>For example, using Pubs as a case in point,
>BookAuthors table,
>PK = TitleID, AuthorID
>FK = TitleID, AuthorID
>
>My understanding is that this would be graded as WRONG on 70-100. The way
>MS wants to see it is:
>BookAuthors Table
>PK = BookAuthorsKey (Surrogate Identity key)
>FK = TitleID, AuthorID
>
>Does anyone have any thoughts/experience about this? I consider the first
>item as correct, but on 70-100, it would be marked WRONG...
>
>Further, if in the case study, if a unique data item is described, such as
>an AccountNo, that MS would not use that as a primary key, but would define
>an independent identity key, e.g., AccountKey as the primary key and
>classify the AccountNo field as "other attributes".
>
>Again, if there was a unique identifier in the "real world", such as an
>AccountNumber or InvoiceNumber, which would be defined as unique in the case
>study, that it should be used as the primary key. My understanding is that
>MS would mark this WRONG on 70-100 and would prefer using a db-generated
>Primary key.
>
>Any opinions/experience as to the "Microsoft Way" would be greatly
>appreciated!!!
>
>
| |
|
| "Erno de Weerd" <ernow@infosupport.com> wrote in message
news:OjqfDT7iCHA.2592@tkmsftngp09...
>
> "Tim" <spammers@begone> wrote in message
> news:OsXYWf6iCHA.1840@tkmsftngp08...
> > But for many-to-many linking tables, like your example of BookAuthors in
> > pubs, I have no problem combining the 2 surrogate keys from related
tables
> > into one composite key. Sometimes those tables won't have any other
> columns
> > at all, so adding a distinct surrogate key here can add a significant
> amount
> > of overhead. And since the composite fields are themselves surrogates,
> they
> > should never need changing either. I don't even show those values to
> users.
>
> Well, I disagree with this statement: as soon as these two FK are combined
> they ARE meaningful!
> And then imagine that you assigned the wrong author to the wrong book and
> referred to this composite MEANINGFUL PK...
I think that's probably the thinking behind the MS 'best practice', and
there's some validity to it.
However, in my experience such linking tables are not usually parents of
other tables. In which case changes to these values would not cascade to
other tables.
Further, since both values are untouchable by users, data entry errors are
less of a factor. They always get some validation due to the foreign key
reference to a parent table - unlike something like 'account number'. And
since they're artificial, they're also unaffected by changes like 'now we
want all account numbers to have seven digits'.
There are cases where it can raise the same problems as a true natural key,
but I find they are rare. And if a distinct surrogate key will add 50% to
the row size (worst case, but also not unusual), there is also a high risk
of a significant performance hit in the most common usage of the table.
Like I said, you do have a valid argument. If I inherited your working
database with these 'extra' surrogates, I'd tend to leave them in place.
| |
| Erno de Weerd 2002-11-14, 10:23 am |
| Tim,
I agree on the performance hit, but it's
> Further, since both values are untouchable by users, data entry errors are
> less of a factor. They always get some validation due to the foreign key
> reference to a parent table - unlike something like 'account number'. And
> since they're artificial, they're also unaffected by changes like 'now we
> want all account numbers to have seven digits'.
that I don't agree with. The value are indeed untouchable BUT the combining
not!
It's not the value that I'm worried about but the added meaning of the
composition.
(Did the author really write that book?!)
Now, as for your argument on these table not being parenttables... well as
soon as you want to add (and that might be after 'going live') information
(in an other table) to the relationship (and that's not really rare); you're
stuck on the composite key and its existing rows...
I'm not trying to say that you're wrong but I do think that this is an
overlooked consequence of composite keys...
Regards,
Erno de Weerd
MCSD MCAD MCT
|
|
|
|
|