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@tortoise.demon.co.uk