On Tue, Jan 22, 2002 at 09:12:28AM +0000, Mark Evans wrote:
There do seem to be some very bizarre design decisions that the designers have made. For example, at the end of an academic year, when pupils are rolled up, a fresh copy of every student is made. So, a given student may appear in 5 or more distinct records in the main pupil table, each with sex, ethnic group etc fields. How often to students change their ethnic group on transition from one year group to the next? Also, some details
Well it's possible for ethnic group to change, especially if the definitions for ethnic groups change. But that's could happen at any time.
That's right: Pupil Name: Wacko Jacko Ethnic Origin: Changeable Sex: Yes please. With young boys ;)
seem saved multiple times across many tables, rather than just being saved once in a single table.
Sounds like someone missed that one of the primary points of a database is not to have multiple copies of the same data.
The proper way to design a database when you're dealing with personal records is to introduce a primary key ie. a personal identifier. This then allows you to break the data down pretty much as you see fit & you should never get duplication of data in tables except for the key of course. I can't imagine that this database front-end doesn't make use of a personal identifier otherwise it would truly be useless. It's also worth investigating what the tables look like by running a SQL query directly on the DB itself. There may be large tables which is generally a bad sign as you straightaway start running into performance issues although with a data set the size of a school it will hardly matter initially but it can come back and bite you. Also when you come to buy a management system such as this always ask for a copy of the ER (entity/relationship) diagram. It could be that the salesman doesn't know what you're on about but tell him to ask the designers for one if that's the case. If they can't provide you with one then tell them to sling their hooks. An ER diagram is an essential tool for database design & if it's properly prosecuted, when the time comes that you need to add a field say, you can look at the ER diagram & see where you can slot it in. Most DB's will produce one but you want one drawn out by hand by the designers using an appropriate graphics tool. I should have one kicking around in which case I'll stick it up on my site so that people on this list will then know what they're looking for. -- Frank *-*-*-*-*-*-*-*-*-*-* Boroughbridge. Tel: 01423 323019 --------- PGP keyID: 0xC0B341A3 *-*-*-*-*-*-*-*-*-*-* http://www.esperance-linux.co.uk/ The gent who wakes up and finds himself a success hasn't been asleep.