Re: help on composite keys!

On Mon, 19 Feb 2001 20:22:52 GMT, arne.b.jonsson_at_telia.com (Arne Jonsson) wrote:

>
>
>We have a SQL-server database. It contains (among others) the tables
>students, certificates and a joining table between these two, called
>student/cert. It looks like this: studentID, certID, date, passed.
>Since a student can fail and make many attempts (so we can have no
>unique identifier) it´s advocated in our group that we shall have no
>pkey in the joining table, just student ID and certID as fkeys . Since
>all the gurus around in this group says there shall always be a pkey
>in a table I try to make my voice heard and tell we must have one and
>if it does´nt work it´s something wrong with the design. But that´s as
>far as my arguments goes. So I call for help to sort this out for me
>(and the others guys int the group). What drawbacks will there be if
>we have this joining table without any pkey? Maybe this is a case for
>Mr Hidders?

Looks like you should have PK student, cert, date, as presumably you can only have one result per date. IMO your pass column should be a result column containing pass/fail.

A binary flag column like pass leads to problems down the road, when you find out that there can be other possible results (like defer, withdraw) or the business decides other possibilities should be added. You then have to either change the original column or add additional binary flag columns.

I would also reconsider the date column. Having a date column allows 365 results per student per cert per year. Is this valid? It should perhaps be year and semester (fall, winter, spring, summer) columns, depending on how often a student can have a result for a certificate.

HTH Thanks. Take care, Brian Inglis Calgary, Alberta, Canada

-- 
Brian_Inglis_at_CSi.com 	(Brian dot Inglis at SystematicSw dot ab dot ca)
				use address above to reply