Another attempt at an open source school database. www.schoolbase.net Anyone with ideas on how such a thing should be made? regards Richard
--- richard@tortoise.demon.co.uk wrote:
Another attempt at an open source school database.
www.schoolbase.net
Anyone with ideas on how such a thing should be made?
I'll try. The parts in speech-marks have been lifted from the website. "I've just started making tables to hold data. What tables/views are needed? Is it best to have 'lookup' tables for ethnicity, religion and so on, or does that assume incorrectly that its known in advance what extra types of data will need to be stored to describe each student? For example, if 'Social class' were needed, following this pattern I'd need an extra lookup table with a corresponding foreign key added to the student table. An alternative would be a table of 'Classifications':" These "lookup" tables are very much an MS-Access terminology. It is possible to have a standalone table which provides values for filtering (known as a non-equi join) but such circumstances where this is used is limited, and almost certainly *very* specific. As for Tables/Views, it's important to remember that under no circumstances do more tables mean a "better design". Second and third form normalisation of database structures tend to teach us that. As for which is best -- it doesn't matter, and it certainly doesn't matter about the datatype present (in this case.) Postgesql is nice because it uses (properly) a good referential-integrity model. So it's important (before *any* data is added into a schema) to actually look at the design of a database schema. Normalisation is a skill that can only really be realised via lots and lots and lots of practise, and I admit to knowing next to nothing about it. That said, perhaps a schema such as the following is not unreasonable: 1 * Class ----------------------Classification (That is to say "One class has many classifications.) Unfortunately, the webpage does not make clear any of the data that's wanted, so the above diagram is no doubt incorrect, based on the needs of the person wanting to implement it. Hence the "Class" table would be the master, and "Classification" the detail. "Class" would therefore have: classId (Primary Key) classification (varchar (255)) and "classification": classfnId (primary Key) classId (foreign key) classfn_name (varchar (255)) Using referential integrity from within postgres (via SQL), it's then a smatter of linking child --> master. So yes, it's certainly possible to do. As for views, they're useful as a "security" measure to restrict users to specific tables, and subsets of results. Only start to llok into views when the correct relationships have been defined and are known to be working. Does that help? I've left out a lot of SQL, a it doesn't look like that's what the person is asking for. -- Thomas Adam ___________________________________________________________ How much free photo storage do you get? Store your holiday snaps for FREE with Yahoo! Photos http://uk.photos.yahoo.com
Hi Thomas Adam
Hence the "Class" table would be the master, and "Classification" the detail. "Class" would therefore have:
classId (Primary Key) classification (varchar (255))
and "classification":
classfnId (primary Key) classId (foreign key) classfn_name (varchar (255))
Using referential integrity from within postgres (via SQL), it's then a smatter of linking child --> master. So yes, it's certainly possible to do.
I've implemented tables class & classification with this design, which you will see if you connect again. (The previous 'look-up' tables are still also there for now.) I've put a bit of data into them. A problem I have with this design, which maybe somebody can give a solution to is this: I now want to say what ethnic group students belong to, and so on, so I need a table like (apologies for word-wrap): create table student_classification( student_classification_id integer unique, student_id integer constraint references student(student_id), classification_id integer constraint references classification(classification_id) ) However, a student belongs falls into at most 1 ethnic group. Therefore I need a constraint on the table to prevent the entry of two records where the same student is assigned 2 classification_id's which actually belong to the same class (ie class_id=2 , Ethnic). A table constraint won't do, as two tables are involved. regards Richard -- richard@tortoise.demon.co.uk
On Sun, 2005-06-05 at 21:07 +0100, richard@tortoise.demon.co.uk wrote: [snip]
I've implemented tables class & classification with this design, which you will see if you connect again. (The previous 'look-up' tables are still also there for now.) I've put a bit of data into them. A problem I have with this design, which maybe somebody can give a solution to is this:
I now want to say what ethnic group students belong to, and so on, so I need a table like (apologies for word-wrap):
create table student_classification( student_classification_id integer unique, student_id integer constraint references student(student_id), classification_id integer constraint references classification(classification_id) )
However, a student belongs falls into at most 1 ethnic group.
Is that right - there is no possibility for mixed ethnicity? I would have thought it would be the other way round, that is at minimum a student belongs to 1 ethnic group.
Therefore I need a constraint on the table to prevent the entry of two records where the same student is assigned 2 classification_id's which actually belong to the same class (ie class_id=2 , Ethnic).
Comment above aside, if you want only one student_classification per student_id, then make student_id in the table above unique - if you do this (if this is what you want) you are creating a 1-1 mapping between a student and their student_classification, so there is no need to have a student_classification_id column, since a record is uniquely identified by the student_id.
A table constraint won't do, as two tables are involved.
regards Richard
-- richard@tortoise.demon.co.uk
--- richard@tortoise.demon.co.uk wrote:
I now want to say what ethnic group students belong to, and so on, so However, a student belongs falls into at most 1 ethnic group. Therefore I need a constraint on the table to prevent the entry of two records where the same student is assigned 2 classification_id's which actually belong to the same class (ie class_id=2 , Ethnic).
A table constraint won't do, as two tables are involved.
So you need to split it up a little. The problem you have is that each classification category itself might have many things associated with it. That's fine, but that calls for a re-think. One solution (ignoring any form of schema design) would be to make each classification below each heading (i.e. 'ethnic group' for instance, might have several elements attached to it) an enum of possibilities. Whilst this is possible, there's several issues with it -- least of which is the fact that you'd then have to use some form of trigger, which is overkill in this case. So what I propose is we revist the schema overall. I don't have a lot of time to spend on this as I'd like. However, I'll outline some of my suggestions. As it stands, the relationship between 'class' and 'classification' as entities is OK. What we need now is a means of linking a student to this, in such a way as to meet the constraint [1] that you're wanting. It seems to me that you want something like the following (I apologise in advance for my poor ASCII diagrams): +--------------+ | Class | +--------------+ | | / \ +-------------------+ +----------------+ | Classification | | Student | +-------------------+ +----------------+ | | | | / \ / \ +----------------------------------------+ | Belief | +----------------------------------------+ The nomenclature for table 'Belief' is questionable, and a more suited name is advised. Essentially. 'Belief' is a link entity. The assumption here is that there is a M-M (many to many) relationship now, between Classification and Student. Due to the referential integrity model, one cannot (directly) implement a M-M relationship between two entities, so a third table is introduced. Essentially, 'Belief' would hold foreign key references to both Classification and Student, so that in this way, 'Belief' could then easily define whether a student could be a member of a group or not, based on table constraints enforced therein. Does that make sense? -- Thomas Adam [1] It's not really a constraint in the sense of a check, it's more a design feature. ___________________________________________________________ How much free photo storage do you get? Store your holiday snaps for FREE with Yahoo! Photos http://uk.photos.yahoo.com
participants (3)
-
Paul Cooper -
richard＠tortoise.demon.co.uk -
Thomas Adam