[thelist] Newbie - DB Design Help

[Ken Schaefer]

: -----Original Message-----
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Rob Smith
: Subject: Re: [thelist] Newbie - DB Design Help
: 
: Bordering on beating a dead horse with an ugly stick, the "bid" field in
: my original M:M relationship, as isolated and unnecessary as it may
: seem, it does allow a certain level of referential integrity especially
: if it's an auto increment field of some sort.

That provides no referential integrity, because it doesn't "reference"
anything.

: You'll never or should
: rather ever get corrupted tables nor allow duplicate data to be entered.

Your DBMS prevents your table getting corrupted. Whether or not there is a
BidID field has nothing to do with the possibility of "corruption". 

Preventing the entering of duplicate data is accomplished by constraints, not
by primary keys per se. Primary keys are there to uniquely identify a row,
not to prevent the entering of duplicate data. It's just that in SQL Server,
creating a Primary Key creates a Unique constraint "behind the scenes" for
you automatically.

: We're teetering on wither bridge tables should be in 4NF or not.

No, we are not. Why are you using terms like "referential integrity" and
"4NF"? We are talking about extraneous fields in your design.

: There could, not that there would, be a class with no students...

Sure. But the class is stored in the "Classes" table. Not in the bridging
table. Even if there are no entries in the bridging table, the class still
exists. 4NF has nothing to do with the bridging table. Again, you are
wondering off into things that have nothing to do with the topic at hand/.

: Every table must have some primary key associated with it...comes in
: handy when you want to do full-text searching, indexing, hashing, SQL's
: containstable if you want to build your own search engine. Anyway.. back
: to work.

But in the case of a bridging table, the primary key is a composite key of
the two foreign keys. Go and look at the schematic I posted.

Cheers
Ken