--- 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