Posted by Robert Klemme on 10/01/94 11:25

Mark wrote:
> I hope it may be clearer if I outline what the tables are for:
>
> I'm basically writing an application that stores information about
> 'behaviour incidents' at a school. The table in question is the
> 'incidents' table which is used to record information about
> individual incidents of negative behaviour (ok - let's call it being
> naughty).
>
> The primary key for the 'incidents' table is made up of an
> 'incidentID' and 'pupilID'. The pupilID indicates the pupil(s) who
> were involved in the incident and is itself a foreign key into a
> 'pupils' table.
>
> This is to reflect the possibility that more than one pupil can be
> involved in the same incident. In this case, there may be for example
> three rows with the same 'incidentID' - each having a unique pupilID
> to reflect one incident in which three different pupils were involved.
>
> My question really revolves around how to generate the 'incidentID'
> that is unique at the time of insertion but allows duplicates if more
> than one pupil is involved.
>
> Can I insert the first row and retrieve the identity with a
> scope_identity and then just insert the rest of the rows with the
> same incidentID? Wouldn't that return an error as the identity column
> wouldn't contain all unique values.

You are right, this table layout would not work with identity. However, I
figure your table layout may not be optimal because you really have a n-m
relationship between incidents and pupils. And as far as I can see
there's no place to store information where there is just one piece per
incident (for example date and time). With all that I know ATM I would
have it as follows:

table incidents with date, time, location whatever and incidentid
(identity)
table pupils with pupilid (identity), name, day of birth - whatever
table pupils_in_incidend with incidentid, pupilid (both foreign keys)

This seems the most normalized approach here.

Kind regards

robert

[Back to original message]