Mailinglist Archive: opensuse-edu (45 mails)
| < Previous | Next > |
Re: [suse-linux-uk-schools] Yet another school database
- From: richard@xxxxxxxxxxxxxxxxxxxx
- Date: Tue, 7 Jun 2005 00:14:08 +0000 (UTC)
- Message-id: <42A4C17C.22147.1031EC7@localhost>
On 6 Jun 2005 at 14:53, Thomas Adam wrote:
> +--------------+
> | Class |
> +--------------+
> |
> |
> / \
> +-------------------+ +----------------+
> | Classification | | Student |
> +-------------------+ +----------------+
> | |
> | |
> / \ / \
> +----------------------------------------+
> | Belief |
> +----------------------------------------+
>
Yes, that's the design I was refering to in an earlier post. I've
implemented it in the database, although I've used the name
student_classification instead of Belief.
I've also made a view v_student_ethnic2 that gets student ethnic data
from student_classification instead of the previous 'look-up' table.
This view, that will show a few records, illustrates my point. What
is to prevent a student being given 2 ethnic groups. In the view,
there is indeed a student with this unwanted duplication.
With a design like this, the only ways I can see to prevent such a
duplication are:
a) A trigger on student_classification that would delete the previous
ethnic group for a given student should a second get inserted.
b) Only allow inserts to student_classification via a stored
procedure that does the checking and adjustments for you.
I've yet to write either a trigger or procedure in PostgreSQL but I
assume such things are easy to do.
However, triggers and procedures immediately make the design non-
portable to other databases. Now I think that in the longer term,
that's inevitable, but I was hoping for a design that could delay the
requirement for such things for as long as possible.
regards
Richard--
richard@xxxxxxxxxxxxxxxxxxxx
> +--------------+
> | Class |
> +--------------+
> |
> |
> / \
> +-------------------+ +----------------+
> | Classification | | Student |
> +-------------------+ +----------------+
> | |
> | |
> / \ / \
> +----------------------------------------+
> | Belief |
> +----------------------------------------+
>
Yes, that's the design I was refering to in an earlier post. I've
implemented it in the database, although I've used the name
student_classification instead of Belief.
I've also made a view v_student_ethnic2 that gets student ethnic data
from student_classification instead of the previous 'look-up' table.
This view, that will show a few records, illustrates my point. What
is to prevent a student being given 2 ethnic groups. In the view,
there is indeed a student with this unwanted duplication.
With a design like this, the only ways I can see to prevent such a
duplication are:
a) A trigger on student_classification that would delete the previous
ethnic group for a given student should a second get inserted.
b) Only allow inserts to student_classification via a stored
procedure that does the checking and adjustments for you.
I've yet to write either a trigger or procedure in PostgreSQL but I
assume such things are easy to do.
However, triggers and procedures immediately make the design non-
portable to other databases. Now I think that in the longer term,
that's inevitable, but I was hoping for a design that could delay the
requirement for such things for as long as possible.
regards
Richard--
richard@xxxxxxxxxxxxxxxxxxxx
| < Previous | Next > |