Re: [suse-linux-uk-schools] Yet another school database
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
--- richard@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 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.
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 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.
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 http://uk.messenger.yahoo.com
participants (2)
-
richard＠tortoise.demon.co.uk -
Thomas Adam