--- richard(a)tortoise.demon.co.uk wrote:
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.
OK. Please bear in mind that the design of that may well be incorrect.
I'm unable to grasp from your website the overall picture of what it
is you're trying to achieve.
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.
Well, a view is just used to lookup information -- so it's about
stopping the data from being entered into the database.
With a design like this, the only ways I can see to
prevent such a
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
procedure that does the checking and adjustments for you.
Both of those would be fine.
I've yet to write either a trigger or procedure
in PostgreSQL but I
assume such things are easy to do.
"Easy" is relative, but there's plenty of examples about how to do it,
so yes... if you can apply that, it should be easy. :)
However, triggers and procedures immediately make the
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.
I don't think you really need to worry about portability. All versions
of RDBMses have a degree of non-portability, no matter how closely they
follow the SQL-92 standard. You will never have a design that
portable, unless it is a very very very simple schema.
-- Thomas Adam
Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail